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

    FROMSOP10100 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

    FROMSOP30200 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.INFO

    FROM(
    SELECT LEFT(SLPRSNID,2) AS REP,INFO
    FROM @Stage
    WHERE SLPRSNID =@MinRep
    ) Z

    -- For testing: See that it is getting the right data
    SELECT @Concat

    SELECT @Message = @Message + ',' + @Concat

    SELECT@MinRep = MIN(SLPRSNID),
    @Concat = NULL
    FROM@Stage
    WHERESLPRSNID > @MinRep

    END

    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)

  •  

    quoteThe 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)

  • 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