T-SQL: Dynamic Where clause in normal SQL statement

Question

Hey there,

I looking for a way to dynamicly add a filter to my statment without using dynamic SQL.

I want to select all computers from a table, but when I pass a computer id to the sp, I want to get only this computer.

Actually I try this on

DECLARE @ComputerFilter AS INT
DECLARE @ComputerID AS INT

SELECT Computername 
FROM   Computer
WHERE  (ComputerID = @ComputerID) OR (@ComputerFilter IS NULL))

But this is 100 times slow then this statment and tooks as long as SELECT * FROM Computer

SELECT Computername 
FROM   Computer
WHERE  ComputerID = @ComputerID

Is there a way to speed this statment up or is there any other way to solve this problem with one select und without dynamic sql?

Answer 1

The easiest way would be to use IF statement.

IF @ComputerID IS NULL BEGIN
  SELECT Computername 
  FROM   Computer
END
ELSE BEGIN
  SELECT Computername 
  FROM   Computer
  WHERE  ComputerID = @ComputerID
END

Answer 2

SQL Server is often bad at optimising queries with an OR in the WHERE clause. Sometimes a UNION or UNION ALL is better. In your case, perform the @ComputerFilter IS NULL check first in in IF statement, and just do a SELECT without a WHERE if true...

Answer 3

Do you have indexing on the table? That should help performance.

Answer 4

hi,

DECLARE @ComputerID AS INT

SET @ComputerID  = null -- fro all null or just id for one

SELECT Computername 
FROM   Computer
WHERE  ComputerID = ISNULL( @ComputerID, ComputerID  )

When you dont need

Answer 5

There is no "one size fits all" query approach for this, there are subtle performance implications in how you do this. If you would like to go beyond just making the query return the proper answer, no matter how slow it is, look at this article on Dynamic Search Conditions in T-SQLby Erland Sommarskog