CODEWITHSUNDEEP
sqlsql-servert-sqlnullinteger
Piercy
Piercy

Reputation: 809

T-SQL Where int=@var / @var=null?

I am having a situation where I call a stored procedure and pass in some variables. In some cases i will be passing in null and I want it to return everything. Take the following (age is an Integer column): 

[Table]  
[Name] | [Age]  
Mike   | 22  
Fred   | 18  
Bob    | 22 



SELECT * FROM [table] WHERE [Age]=@AgeVar

Now if I pass in 22 I will get Mike and Bob. Similarly passing in 18 will get me Fred. However, What if I want all 3 rows when I pass in null/don't set @AgeVar? This is my issue on a very simple scale.

Edit: The Sproc is pretty complex and i don't really want to double all the sections up by having to but them twice as in one of the answers below. Are there any other methods?

Upvotes: 2

Views: 2021

Answers (4)

Will Russell
Will Russell

Reputation: 332

This will give you MUCH better performance than the [Age]=@AgeVar OR @AgeVar IS NULL method, because it will generate index seeks instead of index scans:

SELECT * FROM [Table1]
WHERE COALESCE(@AgeVar, Age) = Age

It leaves your code fairly readable, especially when you have multiple parameters that you may or may not be using to search, and still gives you reasonable performance.

Upvotes: 1

Justin Pihony
Justin Pihony

Reputation: 67075

I have found that if you have a common case, then use a CASE statement. This will make the performance drag only in the rare case. So, if you more often pass a null in as the parameter, then this will be more efficient.

SELECT * 
FROM [table] 
WHERE 
    0 = 
        CASE 
            WHEN @AgeVar IS NULL THEN 0
            WHEN [Age]=@AgeVar THEN 0
        END

Upvotes: 1

Chris Shain
Chris Shain

Reputation: 51329

SELECT * FROM [table] WHERE [Age]=@AgeVar OR @AgeVar IS NULL

As mentioned numerous times above and below, this technique will kill the performance of your queries for large tables. User beware.

Upvotes: 9

GarethD
GarethD

Reputation: 69759

IF @AgeVar IS NULL 
    BEGIN
        SELECT  *
        FROM    @Table
    END
ELSE
    BEGIN
        SELECT  *
        FROM    @Table
        WHERE   Age = @AgeVar
    END

Upvotes: 3

Related Questions