Help needed on concatination of data into variable problem
-
I've worked on this thing for hours and just can't figure out where I'm going wrong. Everything works but the last step.
I've got some data which I need to put into a single text string which will be sent by email to ultimately be delivered by SMS.
Getting the data is fine, sending the email is fine, my loop is getting the data fine. The problem seems to be concatenating the data into the single variable which seems simple enough using
SELECT @Message = @Message + ',' + @Concat
@Concat is generated in the loop, and I've output @Concat in the loop for testing and the data is returned correctly. @Message is always null.
At this point I'm pulling my hair out because I can't see the problem. I'm just to close to it to see it.
Any help would be greatly appreciated.
Thanks,
Mark
SET NOCOUNT ON
--Get the data
DECLARE @Stage TABLE
(
SLPRSNID VARCHAR(15),
PHONE VARCHAR(21),
INFO VARCHAR(9)
)
INSERT @Stage
SELECT REP, MIN(PHONE) AS PHONE, CONVERT(VARCHAR(9),SUM(TOTAL)) AS INFO
FROM (
SELECT RTRIM(R.SLPRSNID) AS REP,
LEFT(R.PHONE2,11) AS PHONE,
CONVERT(MONEY,DOCAMNT) as TOTAL
FROM SOP10100 S INNER JOIN RM00301 R ON S.SLPRSNID = R.SLPRSNID
WHERE S.docdate BETWEEN convert(datetime, convert(varchar(4),YEAR(getdate()))+'/'+convert(varchar(4),MONTH(GETDATE()))+'/01') and getdate()
AND R.INACTIVE = 0
AND R.SLPRSNID <> 'HOUSE'
AND R.PHONE2 LIKE '614%'
AND S.SOPTYPE = 3
UNION SELECT RTRIM(R.SLPRSNID) AS REP,
LEFT(R.PHONE2,11) AS PHONE,
CONVERT(MONEY,DOCAMNT) as TOTAL
FROM SOP30200 S INNER JOIN RM00301 R ON S.SLPRSNID = R.SLPRSNID
WHERE S.docdate BETWEEN convert(datetime, convert(varchar(4),YEAR(getdate()))+'/'+convert(varchar(4),MONTH(GETDATE()))+'/01') and getdate()
AND R.INACTIVE = 0
AND R.SLPRSNID <> 'HOUSE'
AND R.PHONE2 LIKE '614%'
AND S.SOPTYPE = 3
) S
GROUP BY REP
-- Do the work
DECLARE @Message VARCHAR(4000),@Concat VARCHAR(15), @MinREP VARCHAR(15), @MaxREP VARCHAR(15), @rc INT,
@CRLF char(2), @EmailAdd VARCHAR(30), @User Varchar(15), @Start VARCHAR(15), @End VARCHAR(15)
select @Start = convert(varchar(15),convert(datetime,+convert(varchar(3),MONTH(GETDATE()))+'/1/'+convert(varchar(4),YEAR(getdate()))),3),
@End = Convert(varchar(15),GetDate(),3)
-- For Testing: Show source data for message
SELECT *
FROM @Stage
SELECT @MinREP = MIN(SLPRSNID),
@MaxREP = MAX(SLPRSNID)
FROM @Stage
WHILE @MinREP <= @MaxREP
BEGIN
SELECT @Concat = Z.REP+':'+ Z.INFOFROM (
SELECT LEFT(SLPRSNID,2) AS REP,INFO
FROM @Stage
WHERE SLPRSNID =@MinRep
) Z-- For testing: See that it is getting the right data
SELECT @ConcatSELECT @Message = @Message + ',' + @Concat
SELECT @MinRep = MIN(SLPRSNID),
@Concat = NULL
FROM @Stage
WHERE SLPRSNID > @MinRepEND
SELECT @Message AS Message
DELETE FROM @Stage
SET @Concat = NULL
SET @Message = NULL
-
I don't know if I understand the problem correctly but if you question why your @message is NULL at the end of the loop then the answer is because it is NULL at the begining of the loop. Concatenating value to a NULL variable still leaves you with NULL.
If I did not get the problem right then please explain.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
SELECT @Message = ISNULL(@Message + ',', '') + @Concat
_____________
Code for TallyGenerator -
And you don't need loop for this.
Replace whole WHILE thing with this:
SELECT @Message = ISNULL(@Message + ',', '') + Z.REP+':'+ Z.INFO
FROM (
SELECT LEFT(SLPRSNID,2) AS REP,INFO
FROM @Stage
WHERE SLPRSNID =@MinRep
) Z
ORDER BY Z.REP+':'+ Z.INFO
_____________
Code for TallyGenerator -
Sergiy,
do you always hand out everything on a golden plate?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
Not really.
Only stuff not available from BOL.
Ask Jeff about this.
_____________
Code for TallyGenerator -
Actually I was referring to you first post while you were adding that little bonus of the concatenation when you do a select into a variable.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
The moral of the story is:
NULL + (anything) = NULL
-
Wow, thanks for the fast response!
Stepped out for an hour and I've come back to not only the solution but an improvement on the design.
For future reference, I understand the ISNull converting a null @message to empty, could this be accomplised by 'initializing' the bucket by using something like:
SELECT @Message = ''
I realise the solution given is cleaner, just want to get my head around it.
Thanks again,
Mark
-
It works fine with the SELECT @Message = ISNULL(@Message + ',', '') + @Concat replacement, but I can't get it working with Sergiy's solution.
It's probably just the way that I am explaining what I'm trying to accomplish. The @Message bucket should end up having the results from every record in it. This will then be sent by email using another DO WHILE using XP_SMTP. The same @Message will be sent every time, it won't change depending on the record.
If there is indeed a way to do it without the WHILE, I'd love to learn.
Thanks,
Mark
-
It's always a golden plate... and a lot of it isn't available on the web until Serqiy puts it there...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
The same @Message will be sent every time, it won't change depending on the record. Ok... dunno about the others but you just confused the heck out of me. If @Message is never supposed to change, why are you concatenating things to it???
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Sorry for the confusion. @Message does change, basically it grows, adding the results from the previous record, until there are no records left in @Stage table (from @MinRep to @MaxRep). The final @Message is what I need to use further on.
Doing it the other way gave me an @Message that just had the details from the last record in @Stage table (WHERE SLPRSNID =@MinRep).
Mark
-
What's exactly gives you a trouble with it?
_____________
Code for TallyGenerator -
I think the problem comes from the 'golden plate'
SELECT @Message = ISNULL(@Message + ',', '') + Z.REP+':'+ Z.INFO
FROM (
SELECT LEFT(SLPRSNID,2) AS REP,INFO
FROM @Stage
WHERE SLPRSNID =@MinRep
) Z
ORDER BY Z.REP+':'+ Z.INFO
should be
SELECT @Message = ISNULL(@Message + ',', '') + Z.REP+':'+ Z.INFO
FROM (
SELECT LEFT(SLPRSNID,2) AS REP,INFO
FROM @Stage
) Z
ORDER BY Z.REP+':'+ Z.INFO
If I read this correctly he already prefilters all the data into the @Stage table and the
WHERE SLPRSNID =@MinRep
is just used for looping
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply