user1800674 user1800674 - 4 months ago 8x
SQL Question

SQL case expression using nullable parameters

I pass a list of parameters to my stored procedure and some of them can be nullable.

Based on the values passed I need to create a where clause; What I'm trying to do is if @productType is not null then WHERE PmTyId = @productType ELSE dont add it to the where clause.

How do I achieve that?

WHEN @productType IS NOT NULL THEN PmTyId = @productType


Normally you don't use a case in a where clause. You can solve this with simple boolean logic

WHERE (@productType IS NULL OR PmTyId = @productType)