Using Null variables in conditions in T-Sql

Question

Is there a way to use a variable in the following manner?

DECLARE @ID int;
SET @ID = NULL;
SELECT *
FROM Market market
WHERE market.ID IS @ID

Or is there another way to do this?

Thanks.

Answer 1

You need to do

SELECT *
FROM Market market
WHERE market.ID = @ID OR (@ID IS NULL AND market.ID IS NULL)

Just for completeness at the moment it is still possible to do

SET ANSI_NULLS OFF

SELECT *
FROM Market market
WHERE market.ID = @ID

but you shouldn't. This option is deprecated. Quite an interesting related blog post

Answer 2

Is there a particular reason that you are trying to stick null into a variable rather than just doing:

SELECT *
FROM Market market
WHERE market.ID IS NULL