How to structure SQL Query to allow for NULLs

Question

I am using ANSI_NULLS set to ON. I need to update my query to allow for a parameter to pass in a null value. The query I have below has a CASE statement in the JOIN. SQL Server does not like the syntax.

I need to have the JOIN look for NULLs if the @MyColumn parameter is NULL or look for the value that was passed in. With ANSI_NULLS set to ON, I can't just simply set MyColumn = @MyColumn since NULL equates to NOTHING. I know I need to use IS NULL syntax.

How do I correctly format this query to get the expected results?

SET ANSI_NULLS ON 
GO

CREATE PROCEDURE MySampleProc
    @MyColumn INT
AS
    SELECT  
        t2.MyColumn     
    FROM 
        Table1 t1
    JOIN 
        Table2 t2 ON t2.Table2PK = t1.Table2FK 
                  AND CASE 
                          WHEN @MyColumn IS NULL THEN MyColumn IS NULL 
                          ELSE MyColumn = @MyColumn 
                      END

Rumbleweed · Accepted Answer · 2016-01-21 18:42:51Z

3

Try this...

SELECT  
    t2.MyColumn     
FROM Table1 t1
JOIN Table2 t2 ON t2.Table2PK = t1.Table2FK AND 
    ((@MyColumn IS NULL AND MyColumn IS NULL) OR MyColumn = @MyColumn)

answered Jan 21, 2016 at 18:42

Rumbleweed

3701 silver badge9 bronze badges

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

Collectives™ on Stack Overflow

How to structure SQL Query to allow for NULLs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related