T SQL - null variable

Question

I have a stored procedure being called from an .aspx.cs page. I have a parameter that sometimes cannot be sent when the sproc is called. Because of this I'm doing the following:

 IF @variable is null
     BEGIN
         ...do this...
     END
 Else 
         ...do that...

My problem is in the IF statement. As far as I can tell when I use any of the following:

• if @parameterVariable = null
• if @parameterVariable = ''
• if @parameterVariable <= 0

Nothing happens!? When I debug the sproc in SSMS I find that (even though the parameter is empty (no user selection)) that the cursor goes to and runs the code in the ELSE statement. Am I doing something wrong?

Thanks!

Solution 1

if @parameterVariable = null is wrong. Change it to if @parameterVariable IS NULL.

Here is a SQL Fiddle demonstrating this: http://www.sqlfiddle.com/#!6/6cb42/1

Solution 2

use optional parameter:

 CREATE PROCEDURE uspTest
    @param1 varchar(50) = null,

AS
    BEGIN
        SELECT col1, col2
        FROM Table1
        WHERE
                ((@Param1 IS NULL) OR (col1 = @Param1)) 
    END

Solution 3

when debugging in SMSS, you must check the box that says "Pass null value". otherwise your value is an empty string or somesuch.

I use the pattern you suggest all the time, and it works well for me.

Solution 4

i suggest you to read this page => ANSI NULLS

actually if @var = null is not wrong, everything depends on the value of ANSI_NULLS :)

View more solutions

Comments

[asawyer]

Is @parameterVariable actually null ?

[Computer Guy]

I'm using Declare @variable and set @variable = null Typically however the parameter will contain a value. However I found and am correcting the sproc to manage when the user doesn't make the selection and the parameter isn't defined.

[asawyer]

You probably have an issue somewhere else, this works fine. See: sqlfiddle.com/#!3/d41d8/6290

[Coding Duchess]

can you post the code for your stored procedure?

[Martin Smith]

All those comparisons with NULL (except for IS NULL) result in unknown not true or false. Read up on three valued logic.

[Computer Guy]

Where can I find the "Pass null value" checkbox?

[Frank Thomas]

Right click a stored proc, and select 'Execute Stored procedure', and fill in the arguments you want.

[Computer Guy]

Thats a great tool. Thanks, still troubleshooting, not sure if this is what I needed yet. (yeppers I'm still noob)

[Robert Kaucher]

Dominic I added a SQL Fiddle as an example.

[Robert Kaucher]

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL. I would argue that this qualifies for "wrong". But really, to each their own!

[Robert Kaucher]

That is why I used quotes. I'm in no way making a moral judgement. But I do think one should use ANSI SQL as much as possible. Just because it is there does not mean it is "good".

[Computer Guy]

This works for other scenarios, no doubt ...and thank you ElenaDBA... however the ((@Param1 IS NULL is the exact issue that caused me to need to handle the entire rest of the procedure in an if else scenario. I'll spare you the details, thank you again!

[Martin Smith]

This option goes back to Sybase days. It is deprecated and as your link says "in a future version of SQL Server, ANSI_NULLS will always be ON"

[fnurglewitz]

ok :) then I admit that it should be "wrong" without quotes :)