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!

Answer 1

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

Answer 2

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