CODEWITHSUNDEEP
sql-server-2008t-sql
Computer Guy
Computer Guy

Reputation: 411

T SQL - null variable

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:

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!

Upvotes: 4

Views: 16324

Answers (4)

Dominic Goulet
Dominic Goulet

Reputation: 8113

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

Upvotes: 3

fnurglewitz
fnurglewitz

Reputation: 2127

i suggest you to read this page => ANSI NULLS

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

Upvotes: 1

Frank Thomas
Frank Thomas

Reputation: 2514

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.

Upvotes: 1

Coding Duchess
Coding Duchess

Reputation: 6909

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

Upvotes: 5

Related Questions