Reputation: 3494
I want that when user send @dispid null then it will check in where clause with DISPID = IS NULL and when user send any value then it will check with that value in below query. Here DISPID is int type and null allowed
SELECT *
FROM orde_
WHERE
CANCELLED = 0
AND DISPID =CASE WHEN @DISPID IS NULL THEN '' ELSE @DISPID END -- HERE i trying to implement
Upvotes: 1
Views: 75
Reputation: 18659
Please try:
WHERE CANCELLED = 0 AND
((DISPID IS NULL and @DISPID IS NULL) OR DISPID=@DISPID)
Upvotes: 2
Reputation: 96057
Using syntax like (@Dispid IS NULL OR dispid = @Dispid )
in the WHERE
can have severe performance implications. This is because SQL Server will cache the query plan based on the first time the statement is run. That means that if @DispID
has a non-NULL
value then when NULL
value is passed the query plan will severely under estimate the number of rows in the query.
There are, therefore, 2 options you could use to avoid the bad caching of the query plan; however I believe the former does not exist in the now completely unsupported 2008 version.
The first (note caveat) is using OPTION RECOMPILE
; which forces the query plan to be recreate each time the query is run. This is easier to write, however, dose come at the cost of not having the plan cached:
SELECT ...
FROM ...
WHERE Cancelled = 0
AND (@Dispid IS NULL OR dispid = @Dispid)
OPTION (RECOMPILE);
The second is to use a dynamic statement:
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'SELECT Column1,' + @CRLF +
N' ...,' + @CRLF + --....etc
N'FROM ...' + @CRLF +
N'WHERE Cancelled = 0' +
CASE WHEN @DispID IS NOT NULL THEN @CRLF + N' AND DispID = @Disp' ELSE N'' END + N';'
EXEC sp_executesql @SQL, N'@DispID int', @DispID; --@DispID data type guessed
This has the advantage that the different statements will have different cached plans and don't need to be recreated each time, however, isn't as easy to understand for some.
Gail Shaw and Aaron Bertrand have both written articles on these Catch-All (or "Kitchen Sink") queries, which will supply good complimentary reading.
Upvotes: 0
Reputation: 1271231
Use simple boolean logic:
where cancelled = 0 and
(@dispid is null or dispid = @dispid)
Upvotes: 1