Looping when string reaches length.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Looping when string reaches length.

Author

Topic

haw001
Starting Member

20 Posts

Posted - 2006-07-26 : 04:29:35

I've got a stored proc which concatonates values in a table and then sends the info by SMS using an email gateway.

SMS has a limit of 150 characters, so if 150 characters is reached, I want the procedure to break the string at the end of a record naturally, not right in middle when 150 characters is reached.

The procedure should then send the email and continue looping until all records are sent.

Here is the code so far, it works without checking the length sending an email for each distinct SLPRSNID:

SET NOCOUNT ON

--Get the data

DECLARE @Stage TABLE
        (
		SLPRSNID VARCHAR(15),
		PHONE VARCHAR(21),
		INFO VARCHAR(100)		
        )

INSERT		@Stage
SELECT 		RTRIM(R.SLPRSNID), 
		LEFT(R.PHONE2,11) AS PHONE,
		RTRIM(CUSTNMBR)+ ' ' + CONVERT(VARCHAR(8),(CONVERT(MONEY,DOCAMNT)))
		
FROM		SOP10100 S INNER JOIN RM00301 R ON S.SLPRSNID =  R.SLPRSNID

WHERE 		S.docdate = convert(datetime, convert(varchar(10), getdate(), 120), 120)
		AND R.INACTIVE = 0 
		AND R.SLPRSNID <> 'HOUSE' 
		AND R.PHONE2 LIKE '614%'
		AND S.SOPTYPE = 2

-- create the single records

DECLARE @Output TABLE (SLPRSNID VARCHAR(15), PHONE VARCHAR(21), SMS VARCHAR(3000), Info VARCHAR(3000))

INSERT		@Output
		(
			SLPRSNID,
			PHONE
		)
SELECT DISTINCT	SLPRSNID, PHONE
FROM		@Stage

-- Do the work

DECLARE @Concat VARCHAR(8000), --Formatted Email for Email2SMS Gateway
 @MinREP VARCHAR(15), @MaxREP VARCHAR(15), @rc INT,
	@CRLF char(2), @EmailAdd VARCHAR(30), @User Varchar(15), @Count INT,
	@Concat2 VARCHAR(8000) --SMS text for counting

set @CRLF = char(10)--+ char(13)
set @EmailAdd = 'x@x.com.au'
set @User = 'x'

SELECT	@MinREP = MIN(SLPRSNID),
	@MaxREP = MAX(SLPRSNID)
FROM	@Output

WHILE @MinREP <= @MaxREP
	BEGIN
		SELECT	@Concat = ISNULL(@Concat,'User:'+@User +@CRLF + 'To:+'+Z.PHONE+@CRLF+'Text:REC:'+@CRLF+@CRLF) +'Text:'+ Z.INFO +','+ @CRLF,
			@Concat2 = ISNULL(@Concat2,'') + Z.INFO +','+ @CRLF
		
		FROM	(
				SELECT PHONE,INFO
				FROM @Stage
				WHERE SLPRSNID =@MinRep
			) Z
		
		UPDATE @Output
		SET 	SMS = @Concat,Info = @Concat2
		WHERE	SLPRSNID =@MinRep

		SELECT LEN(@Concat2)

		exec @rc = master.dbo.xp_smtp_sendmail
			@FROM			= N'server@x.com.au',
			@FROM_NAME		= N'xt',
			@TO			= @EmailAdd,
			@subject		= N'email2sms',
			@message		= @Concat,
			@type			= N'text/plain',
			@server 		= N'mail.x.com.au'
		select RC = @rc
		
		SELECT	@MinRep = MIN(SLPRSNID),
			@Concat = NULL,
			@Concat2 = NULL
		FROM	@Output
		WHERE	SLPRSNID > @MinRep
	END

I was thinking an extra variable, which holds the new data to be appended to @Concat2, and when Len @Concat2 and the new variable is greater than 150 it branches to the email routine and sends @Concat and starts over.

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-26 : 04:48:43

What if you would do something like this?

DECLARE @LastCharPosBeforeCR INT,
        @150Chars VARCHAR(150)

SELECT  @LastCharPosBeforeCR = 150 - CHARINDEX(@CrLf, REVERSE(LEFT(@Concat, 150)))
SELECT  @150Chars = LEFT(@Concat, @LastCharPosBeforeCR)

This code will truncate your concatenated string at the character before last CrLf to the left of the 150 character limit.

Then you can continue your loop with

SELECT  @Concat = SUBSTRING(@Concat, @LastCharPosBeforeCR + 2, 8000)

Peter Larsson
Helsingborg, Sweden

haw001
Starting Member

20 Posts

Posted - 2006-07-26 : 04:56:27

That might work with a nested while statement. Simpler than the route I was taking.

I'll give it a try and post the results.

Still might have to do it the other way if anyone else is doing something similar without a unique character to split on, so if anyone has another solution I'll test it as well.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-26 : 05:07:56

But you have a WHILE statement, right?

My suggestion for solution was to put my code before your UPDATE statement and change the SET SMS = @Concat to SET SMS = @150chars.

Sometihn like this

DECLARE @Stage TABLE
        (
		SLPRSNID VARCHAR(15),
		PHONE VARCHAR(21),
		INFO VARCHAR(100)		
        )

INSERT		@Stage
SELECT 		RTRIM(R.SLPRSNID), 
		LEFT(R.PHONE2,11) AS PHONE,
		RTRIM(CUSTNMBR)+ ' ' + CONVERT(VARCHAR(8),(CONVERT(MONEY,DOCAMNT)))
		
FROM		SOP10100 S INNER JOIN RM00301 R ON S.SLPRSNID =  R.SLPRSNID

WHERE 		S.docdate = convert(datetime, convert(varchar(10), getdate(), 120), 120)
		AND R.INACTIVE = 0 
		AND R.SLPRSNID <> 'HOUSE' 
		AND R.PHONE2 LIKE '614%'
		AND S.SOPTYPE = 2

-- create the single records

DECLARE @Output TABLE (SLPRSNID VARCHAR(15), PHONE VARCHAR(21), SMS VARCHAR(3000), Info VARCHAR(3000))

INSERT		@Output
		(
			SLPRSNID,
			PHONE
		)
SELECT DISTINCT	SLPRSNID, PHONE
FROM		@Stage

-- Do the work

DECLARE @Concat VARCHAR(8000), --Formatted Email for Email2SMS Gateway
 @MinREP VARCHAR(15), @MaxREP VARCHAR(15), @rc INT,
	@CRLF char(2), @EmailAdd VARCHAR(30), @User Varchar(15), @Count INT,
	@Concat2 VARCHAR(8000) --SMS text for counting

set @CRLF = char(10)--+ char(13)
set @EmailAdd = 'x@x.com.au'
set @User = 'x'

DECLARE @LastCharPosBeforeCR INT,
        @150Chars VARCHAR(150)
SELECT	@MinREP = MIN(SLPRSNID),
	@MaxREP = MAX(SLPRSNID)
FROM	@Output

WHILE @MinREP <= @MaxREP
	BEGIN
		SELECT	@Concat = ISNULL(@Concat,'User:'+@User +@CRLF + 'To:+'+Z.PHONE+@CRLF+'Text:REC:'+@CRLF+@CRLF) +'Text:'+ Z.INFO +','+ @CRLF,
			@Concat2 = ISNULL(@Concat2,'') + Z.INFO +','+ @CRLF
		
		FROM	(
				SELECT PHONE,INFO
				FROM @Stage
				WHERE SLPRSNID =@MinRep
			) Z

		SELECT  @LastCharPosBeforeCR = 150 - CHARINDEX(@CrLf, REVERSE(LEFT(@Concat, 150)))
		SELECT  @150Chars = LEFT(@Concat, @LastCharPosBeforeCR)
		SELECT  @Concat = SUBSTRING(@Concat, @LastCharPosBeforeCR + 2, 8000)		
		UPDATE @Output
		SET 	SMS = @150chars,
			Info = @Concat2
		WHERE	SLPRSNID =@MinRep

		SELECT LEN(@Concat2)

		exec @rc = master.dbo.xp_smtp_sendmail
			@FROM			= N'server@x.com.au',
			@FROM_NAME		= N'xt',
			@TO			= @EmailAdd,
			@subject		= N'email2sms',
			@message		= @Concat,
			@type			= N'text/plain',
			@server 		= N'mail.x.com.au'
		select RC = @rc
		
		SELECT	@MinRep = MIN(SLPRSNID),
			@Concat = NULL,
			@Concat2 = NULL
		FROM	@Output
		WHERE	SLPRSNID > @MinRep
	END

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources