T SQL - null variable

Question

I have a stored procedure called from the .aspx.cs page. I have a parameter that sometimes cannot be sent when sproc is called. Because of this, I do the following:

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

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

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

Nothing happens!? When I debug sproc in SSMS, I find this (although the parameter is empty (without user selection)), to which the cursor jumps and runs the code in the ELSE statement. Am I doing something wrong?

Thanks!

Answer 1

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

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

Answer 2

use an optional parameter:

  CREATE PROCEDURE uspTest @param1 varchar(50) = null, AS BEGIN SELECT col1, col2 FROM Table1 WHERE ((@Param1 IS NULL) OR (col1 = @Param1)) END 

Answer 3

when debugging SMSS, you must select the "Send null" checkbox. otherwise your value will be an empty string or somesuch.

I use the template that you offer all the time and it works well for me.

Answer 4

I suggest you read this page => ANSI NULLS

Actually, if @var = null is wrong, it all depends on the value of ANSI_NULLS :)