Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to check if string variable is null?

Answered How to check if string variable is null?

  • Friday, February 01, 2013 11:22 AM
     
     
    Sign In to Vote
    0

    Hello guys!
    -----------------------------------------------------
    I have an SQL Server StoredProcedure which has an output parameter @ProcOutput of the type VARCHAR.
    The output value can be:
     - an empty string;
     - not an empty string;
     - the NULL;
    After the StoredProcedure has been executed, I have to analize the value of the output parameter in a Precedence Constraint.
    -----------------------------------------------------
    To do this, I created a package variable named PackVar of type STRING.
    Then I mapped the StoredProcedure output parameter to the package variable PackVar.
    But when the StoredProcedure returns NULL, my Precedence Constraint does not work - the PackVar equals an empty string.
    This is inappropriate to me as NULL and Empty String are to be treated differently in the package.
    My Precedence Constraint is written the next way:
     - Value:      Success
     - Expression: ISNULL(@PackVar)
    -----------------------------------------------------
    Could you please advise me how to resolve the issue?

    Thanks in advance,
    BorkaS

     

All Replies

  • Friday, February 01, 2013 12:02 PM
     
      Has Code
    Sign In to Vote
    0

    Can you modify your SQL query and give a case condition and get the status. like this 

    select Case when cust_status is null then 1 when cust_status='' then 2 else 3 end Status from Table

    Then you use 1,2,3 in the precedence constraint to seperate

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.


    • Edited by Eswararao C Friday, February 01, 2013 12:03 PM
    •  
     
  • Friday, February 01, 2013 12:16 PM
     
      Has Code
    Sign In to Vote
    0

    Can you modify your SQL query and give a case condition and get the status. like this 

    select Case when cust_status is null then 1 when cust_status='' then 2 else 3 end Status from Table

    Then you use 1,2,3 in the precedence constraint to seperate

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.


    Hi Eswararao C!

    I am afraid, that is barely possible...
    There are some reasons for it:
    1) I need that STRING output in the package - I use it as an input for some procedures further.
    2) Technically yes, I could do it, but we disign our procedures in accordance with the company pattern.
       That means, I will be supposed to change all the existing procedures to have an additional Output = {1, 2, 3};
    I should admit that your idea is quite smart, but I would prefer not to change the store procedure - in my case that is undesirable.

    Thanks,
    BorkaS


     
  • Friday, February 01, 2013 12:31 PM
     
     Proposed Has Code
    Sign In to Vote
    1
     
    Declare @tablevar table(col1 varchar(10))
insert into @tablevar(col1) exec test_status
SELECT col1,case when col1 is null then 1 when col1='' then 2 else 3 end status FROM @tablevar

    You can do this in exeucte sql task. You have map two variables one is actual column and other is status and validate status

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    • Proposed As Answer by Russ Loski Friday, February 01, 2013 1:50 PM
    •  
     
  • Friday, February 01, 2013 12:33 PM
    Moderator
     
     
    Sign In to Vote
    0

    Since you are unable to change the proc evaluate the variable to see if it is NULL and use an IF expression to incorporate the path in the precedent constratint.  I cover using an IF expression here:

    http://sqlsafety.blogspot.com/2013/01/ssis-if-expression-as-case-statement.html

    Checking for NULLs is simple using this syntax:

    ISNULL(<YourVariableName>)

    Hope this helps

    David Dye My Blog

     
  • Friday, February 01, 2013 1:27 PM
     
     
    Sign In to Vote
    0

    Since you are unable to change the proc evaluate the variable to see if it is NULL and use an IF expression to incorporate the path in the precedent constratint.  I cover using an IF expression here:

    http://sqlsafety.blogspot.com/2013/01/ssis-if-expression-as-case-statement.html

    Checking for NULLs is simple using this syntax:

    ISNULL(<YourVariableName>)

    Hope this helps

    David Dye My Blog

    Excuse me, it is a little unclear to me.
    It seems that I am unable to check if the variable is NULL since its type which is STRING does not allow it to keep the NULL.

    Thanks,

    BorkaS

     
  • Friday, February 01, 2013 1:29 PM
     
      Has Code
    Sign In to Vote
    0
     
    Declare @tablevar table(col1 varchar(10))
insert into @tablevar(col1) exec test_status
SELECT col1,case when col1 is null then 1 when col1='' then 2 else 3 end status FROM @tablevar

    You can do this in exeucte sql task. You have map two variables one is actual column and other is status and validate status

    Regards,Eshwar.

    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Thanks, Eswararao C

    I will be keeping your solution in mind.

    Regards,

    BorkaS

     
  • Friday, February 01, 2013 1:40 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    I apologize.  I overlooked the data type.  You are correct.  The NULL value is changed to an empty string.  I agree that NULL and an empty string are completely different, but unfortunately there is no way to handle this type of logic within the package, differentiate between a NULL and an empty string.  You would have to modify the stored procedure to change the parameter value if a NULL value is present.  You could use ISNULL(@parameter, 'NULL'), which would replace the ANSI NULL value to a string of NULL and then update your precedent constraint.  If the value is not NULL, an empty string, then the existing evaluations remain the same and will still work.

    Hope this helps

    David Dye My Blog

     
  • Friday, February 01, 2013 3:40 PM
     
     
    Sign In to Vote
    0

    I apologize.  I overlooked the data type.  You are correct.  The NULL value is changed to an empty string.  I agree that NULL and an empty string are completely different, but unfortunately there is no way to handle this type of logic within the package, differentiate between a NULL and an empty string.  You would have to modify the stored procedure to change the parameter value if a NULL value is present.  You could use ISNULL(@parameter, 'NULL'), which would replace the ANSI NULL value to a string of NULL and then update your precedent constraint.  If the value is not NULL, an empty string, then the existing evaluations remain the same and will still work.

    Hope this helps

    David Dye My Blog

    Thanks David!

    BorkaS

    • Edited by BorkaS Friday, February 01, 2013 3:41 PM put my comments in the wrong place
    •