CODEWITHSUNDEEP
t-sqlnull
triangulito
triangulito

Reputation: 202

Using Null variables in conditions in T-Sql

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.

Upvotes: 4

Views: 2895

Answers (3)

bobs
bobs

Reputation: 22184

If you just want to determine if market.ID is null, you would do this

SELECT *
FROM Market market
WHERE market.ID IS NULL

If you're testing to see if market.ID is null or some other value then try this

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

Upvotes: 0

Cubicle.Jockey
Cubicle.Jockey

Reputation: 3328

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

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453287

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

Upvotes: 12

Related Questions