Geek Answers Handbook

T-SQL isnull in which execution conditions

I am looking for opinions and perhaps a concrete answer to the following.
This question applies to SQL Server version 2008 R2 +

In the stored procedure, I have an optional query parameter of type DATE, you can call it @MyVar.

The stored procedure executes the following query:

SELECT A, B, C
FROM MyTable
WHERE MyTable.Field1 = ISNULL(@MyVar,MyTable.Field1)

What is the cost of doing ISNULL (@MyVar, MyTable.Field1) if @MyVar is NULL? I am wondering if it is better to break up cases such as:

IF (@MyVar IS NULL)
    SELECT A, B, C
    FROM MyTable
ELSE
    SELECT A, B, C
    FROM MyTable
    WHERE MyTable.Field1 = @MyVar

Thank!

+4
source share
3 answers

More elegant approach:

SELECT A, B, C
FROM   MyTable
WHERE  MyTable.Field1 = @MyVar
   OR  @MyVar IS NULL
OPTION(RECOMPILE)

change

Based on @JamesZ's suggestion, added option(recompile)to use index field1(if one exists).

edit2 ( @Y.B. .)

field1 null - :

SELECT A, B, C
    FROM   MyTable
    WHERE  MyTable.Field1 = @MyVar
       OR  (@MyVar IS NULL AND MyTable.Field1 IS NULL)
    OPTION(RECOMPILE)

" " . , , AND OR, () , .

+10

2 , @MyVar, - . , , , (. sniffing).

, .

, view

SELECT A, B, C
    FROM MyTable

, . . , .

0

note that

SELECT A, B, C
FROM MyTable
WHERE MyTable.Field1 = ISNULL(@MyVar,MyTable.Field1)

will not return records where Field1 is NULL, whereas

IF (@MyVar IS NULL)
    SELECT A, B, C
    FROM MyTable
ELSE
    SELECT A, B, C
    FROM MyTable
    WHERE MyTable.Field1 = @MyVar

will be.

The technically correct and efficient way to rewrite the original expression is as follows:

SELECT A, B, C
FROM MyTable
WHERE @MyVar IS NULL AND MyTable.Field1 IS NOT NULL
UNION ALL
SELECT A, B, C
FROM MyTable
WHERE @MyVar IS NOT NULL AND MyTable.Field1 = @MyVar
0
source

More articles:


All Articles