Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unhappy Unanswered: Variabled 'NULLED' by END in Stored Procedure

    I have written a stored procedure that is fired by a trigger. Basically the plan is that the trigger sends some details to the SP which looks up the values in the database and emails it to a salesbloke.

    I build a message header and put it in a local variable and another local variable is used to collect the information from the query which is done via a cursor as below:

    OPEN dl_cursor
    FETCH NEXT FROM dl_cursor INTO @dl1, @dl2, @dl3, @dl4
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @msg2 = ISNULL(@msg2,'') + @dl1 + ' - ' + @dl2 + ' - ' + @dl4 +char(10)
    FETCH NEXT FROM dl_cursor INTO @dl1, @dl2, @dl3, @dl4
    END

    By putting a PRINT @msg2 line before the END I can see that the correct information is being built up. However when I test @msg2 after the END it is NULL!!!!

    This is not good Can anybody suggest a workaround?

    Tim

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    A trigger...that calls a sproc...that uses a cursor...to send email...

    Uhh no...can't help...

    hope it a low volume oltp
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    2

    Red face

    Forget about it - it was an entirely wrong diagnosis!!!

    Some of my DB data was NULL and if you concatenate a string and a NULL you get...


    ...NULL

    DOH!!!

    It's late on friday - that's my excuse.

    Tim

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...

    If any value in a string is concatenated with a null, the whole string is Nul

    Just use ISNULL

    DECLARE @x char(10, @y char(1)

    SELECT @x = Null, @y = 'X'

    SELECT 'Howdy ' + @x + @y

    SELECT 'Howdy ' + ISNULL(@x,'') + ISNULL(@y,'')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •