CODEWITHSUNDEEP
sqlsql-serversql-server-2008
A.Goutam
A.Goutam

Reputation: 3494

how to check is null in case and if parameter is null then check value with IS NULL

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

Answers (3)

TechDo
TechDo

Reputation: 18659

Please try:

WHERE CANCELLED = 0 AND
    ((DISPID IS NULL and @DISPID IS NULL) OR DISPID=@DISPID)

Upvotes: 2

Thom A
Thom A

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

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Use simple boolean logic:

where cancelled = 0 and
      (@dispid is null or dispid = @dispid)

Upvotes: 1

Related Questions