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?
WHERE
CASE
WHEN @productType IS NOT NULL THEN PmTyId = @productType
END
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)
One option:
WHERE ISNULL(@productType, PmTyId) = PmTyId
I'd do it like this. Wrapping in parenthesis will allow you to add more in the WHERE clause;
Ok, test data;
IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData
GO
CREATE TABLE #TestData (IDField int, PmTyId varchar(20))
INSERT INTO #TestData (IDField, PmTyId)
VALUES
(1,'include')
,(2,'include')
,(3,'dont include')
My query;
DECLARE @productType varchar(20); SET @productType = 'include'
SELECT
IDField
,PmTyId
FROM #TestData
WHERE (@productType IS NULL OR @productType = PmTyId)
Outputs;
IDField PmTyId
1 include
2 include
If @productType is set to null then you get these results;
IDField PmTyId
1 include
2 include
3 dont include
If you need to add further parameters then do something like this;
SELECT
IDField
,PmTyId
FROM #TestData
WHERE (@productType IS NULL OR @productType = PmTyId)
OR (@Parameter2 IS NULL OR @Parameter2 = Field2)
OR (@Parameter3 IS NULL OR @Parameter3 = Field3)
IS NULL/IS NOT NULL :)