locked
If Execute SQL task return value is Null, then what value will be assigned to Variable? RRS feed

  • Question

  • Hi,

    When I use Execute SQL task with return value and if the return value is NULL, then what value would be assigned to variable.

    When I used int variable, it was assigned 0.
                         varchar variable, it was assigned ''.

    For datetime what would be coming?

    So, can you please tell me what is the way for handling this scenario of NULL return?

    Thanks,
    Venkat.
    Thursday, January 22, 2009 10:58 PM

Answers

  • Hi

    I would recommend that you check the result before end of the task by doing something like

    ISNULL(@Result,0) -- default it to 0 or whatever you want if the output is int

    ISNULL(@Result,'DEFAULT') -- default it to 'DEFAULT'or whatever you want if the output is VARCHAR.. same thing with date you might default it to 1900-01-01.

    In short never ever return NULL unless you want NULL as a output. Handle this by ISNULL() in the Execute SQL task

     

     

     

     


    Ashwani Roy - MCITP http://ashwaniroy.spaces.live.com/
    • Proposed as answer by Ashwani Roy Sunday, January 25, 2009 5:17 PM
    • Marked as answer by Tony Tang_YJ Thursday, January 29, 2009 10:18 AM
    Friday, January 23, 2009 4:51 PM
  • ashwani is correct.  an attempt to use null as a return value from sql server stored procedure generates a warning, then returns 0.  thus, null shouldn't be used as a return value:

    "When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned."

    http://msdn.microsoft.com/en-us/library/ms174998.aspx

    hth


    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Proposed as answer by Ashwani Roy Sunday, January 25, 2009 5:18 PM
    • Marked as answer by Tony Tang_YJ Thursday, January 29, 2009 10:18 AM
    Saturday, January 24, 2009 6:47 AM

All replies

  • What are you trying to achieve? How are you going to use the variable?

    Friday, January 23, 2009 1:35 AM
    Answerer
  • Venkataraman R said:

    Hi,

    When I use Execute SQL task with return value and if the return value is NULL, then what value would be assigned to variable.

    When I used int variable, it was assigned 0.
                         varchar variable, it was assigned ''.

    For datetime what would be coming?

    So, can you please tell me what is the way for handling this scenario of NULL return?

    Thanks,
    Venkat.


    you can test for the value of the variable in a script task as follows:


    1Public Sub Main() 
    2Dim strResult As String 
    3Dim vars As Variables 
    4Dts.VariableDispenser.LockOneForRead("DateVariable", vars) 
    5Dim var1 As Object = vars(0).Value 
    6vars.Unlock() 
    7strResult = CStr(var1) 
    8MsgBox("the result is: " & strResult) 
    9Dts.TaskResult = ScriptResults.Success 
    10End Sub 
    11 

    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Proposed as answer by Duane Douglas Friday, January 23, 2009 8:40 AM
    • Unproposed as answer by Duane Douglas Saturday, January 24, 2009 6:49 AM
    Friday, January 23, 2009 7:44 AM
  • Hi

    I would recommend that you check the result before end of the task by doing something like

    ISNULL(@Result,0) -- default it to 0 or whatever you want if the output is int

    ISNULL(@Result,'DEFAULT') -- default it to 'DEFAULT'or whatever you want if the output is VARCHAR.. same thing with date you might default it to 1900-01-01.

    In short never ever return NULL unless you want NULL as a output. Handle this by ISNULL() in the Execute SQL task

     

     

     

     


    Ashwani Roy - MCITP http://ashwaniroy.spaces.live.com/
    • Proposed as answer by Ashwani Roy Sunday, January 25, 2009 5:17 PM
    • Marked as answer by Tony Tang_YJ Thursday, January 29, 2009 10:18 AM
    Friday, January 23, 2009 4:51 PM
  • ashwani is correct.  an attempt to use null as a return value from sql server stored procedure generates a warning, then returns 0.  thus, null shouldn't be used as a return value:

    "When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned."

    http://msdn.microsoft.com/en-us/library/ms174998.aspx

    hth


    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Proposed as answer by Ashwani Roy Sunday, January 25, 2009 5:18 PM
    • Marked as answer by Tony Tang_YJ Thursday, January 29, 2009 10:18 AM
    Saturday, January 24, 2009 6:47 AM
  • Duane Douglas said:

    Venkataraman R said:

    Hi,

    When I use Execute SQL task with return value and if the return value is NULL, then what value would be assigned to variable.

    When I used int variable, it was assigned 0.
                         varchar variable, it was assigned ''.

    For datetime what would be coming?

    So, can you please tell me what is the way for handling this scenario of NULL return?

    Thanks,
    Venkat.


    you can test for the value of the variable in a script task as follows:


    1 Public Sub Main() 
    2 Dim strResult As String 
    3 Dim vars As Variables 
    4 Dts.VariableDispenser.LockOneForRead("DateVariable", vars) 
    5 Dim var1 As Object = vars(0).Value 
    6 vars.Unlock() 
    7 strResult = CStr(var1) 
    8 MsgBox("the result is: " & strResult) 
    9 Dts.TaskResult = ScriptResults.Success 
    10 End Sub 
    11  

    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.



    please disregard this post
    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    Saturday, January 24, 2009 6:50 AM