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!

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. — Computer Guy
– Computer Guy, Commented Nov 16, 2012 at 19:27
You probably have an issue somewhere else, this works fine. See: sqlfiddle.com/#!3/d41d8/6290 — asawyer
– asawyer, Commented Nov 16, 2012 at 19:28
All those comparisons with NULL (except for IS NULL) result in unknown not true or false. Read up on three valued logic. — Martin Smith
– Martin Smith, Commented Nov 16, 2012 at 20:02

Coding Duchess · Accepted Answer · 2012-11-16 19:26:18Z

5

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

answered Nov 16, 2012 at 19:26

Coding Duchess

7,01928 gold badges146 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Computer Guy Over a year ago

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!

Robert Kaucher · Accepted Answer · 2012-11-16 19:55:08Z

3

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

edited Nov 16, 2012 at 19:55

Robert Kaucher

1,8613 gold badges25 silver badges44 bronze badges

answered Nov 16, 2012 at 19:27

Dominic Goulet

8,1437 gold badges33 silver badges57 bronze badges

1 Comment

Robert Kaucher Over a year ago

Dominic I added a SQL Fiddle as an example.

Frank Thomas · Accepted Answer · 2012-11-16 19:27:51Z

1

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.

answered Nov 16, 2012 at 19:27

Frank Thomas

2,5241 gold badge19 silver badges28 bronze badges

3 Comments

Computer Guy Over a year ago

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

Frank Thomas Over a year ago

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

Computer Guy Over a year ago

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

fnurglewitz · Accepted Answer · 2012-11-16 19:42:44Z

1

i suggest you to read this page => ANSI NULLS

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

answered Nov 16, 2012 at 19:42

fnurglewitz

2,12714 silver badges21 bronze badges

4 Comments

Robert Kaucher Over a year ago

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 Over a year ago

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".

Martin Smith Over a year ago

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 Over a year ago

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

Collectives™ on Stack Overflow

T SQL - null variable

4 Answers 4

1 Comment

1 Comment

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related