none
Variable value not set?

    Question

  • DECLARE @validation_message NVARCHAR(200)
    
    BEGIN 
    	SET @validation_message = @validation_message + 'test was here'
    END
    
    SELECT @validation_message

    What silly mistake am I making?  Why is my variable value null?

    However, If I change the code to this, I get the expected result

    DECLARE @validation_message NVARCHAR(200)
    SET @validation_message = ''
    BEGIN 
    	SET @validation_message = @validation_message + 'test was here'
    END
    
    SELECT @validation_message

    Wednesday, August 21, 2013 8:34 PM

Answers

  • Check the flow,

    DECLARE @validation_message NVARCHAR(200)
    SELECT @validation_message
    SET @validation_message=''
    SELECT @validation_message
    BEGIN 
    	SET @validation_message = @validation_message + 'test was here'
    END
    
    SELECT @validation_message
    	
    --or
    go
    DECLARE @validation_message NVARCHAR(200)
    SELECT @validation_message
    
    BEGIN 
    	SET @validation_message = isnull(@validation_message,'') + 'test was here'
    END
    
    SELECT @validation_message
    	

    Concatenating a string with a NULL yields NULL.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Olaf HelperMVP Thursday, August 22, 2013 4:57 AM
    • Marked as answer by shiftbit Thursday, August 22, 2013 11:59 AM
    Thursday, August 22, 2013 12:55 AM

All replies

  • Your variables were intialized with null value then you declare them.

    Check this: http://technet.microsoft.com/en-us/library/ms187953(v=sql.105).aspx


    Wednesday, August 21, 2013 8:42 PM
  • After DECLARE, the variable initiliazed to NULL and when you add that to string, it returned NULL but in the other case, you've an empty character and adding that to string resturns your expected result.

    Narsimha

    • Proposed as answer by Olaf HelperMVP Thursday, August 22, 2013 4:57 AM
    Wednesday, August 21, 2013 10:21 PM
  • You might also want to learn that  declarative languages  like SQL seldom if ever use local variables. Since SQL is database only, we would never post such a message. I would be in a presentation layer. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, August 22, 2013 12:52 AM
  • Check the flow,

    DECLARE @validation_message NVARCHAR(200)
    SELECT @validation_message
    SET @validation_message=''
    SELECT @validation_message
    BEGIN 
    	SET @validation_message = @validation_message + 'test was here'
    END
    
    SELECT @validation_message
    	
    --or
    go
    DECLARE @validation_message NVARCHAR(200)
    SELECT @validation_message
    
    BEGIN 
    	SET @validation_message = isnull(@validation_message,'') + 'test was here'
    END
    
    SELECT @validation_message
    	

    Concatenating a string with a NULL yields NULL.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Olaf HelperMVP Thursday, August 22, 2013 4:57 AM
    • Marked as answer by shiftbit Thursday, August 22, 2013 11:59 AM
    Thursday, August 22, 2013 12:55 AM