You see this kind of question all the time in newsgroups/forums, someone wants to return all the rows if nothing is passed in or just the rows that match the variable when something is passed in. Usually someone will reply with a suggestion to do something like this

WHERE (SomeColumn=@col OR @col IS NULL)

The problem with that approach is that it doesn’t perform well, let’s take a look, first create this table

USE tempdb
GO



CREATE TABLE Test(SomeCol1 INT NOT NULL, Somecol2 INT NOT NULL)

INSERT Test
SELECT number,low FROM master..spt_values
WHERE TYPE = 'p'


CREATE INDEX ix_test ON Test(Somecol2)
GO

Here is the query that uses the method described before, I am using AND 1=1 so that this query will match the one I will show later

DECLARE @col INT
SELECT @col = 1

SELECT SomeCol2 
FROM Test
WHERE 1 =1
AND  (SomeCol2=@col OR @col IS NULL)

Here is the query using dynamic SQL

GO

DECLARE @col INT
SELECT @col = 1

DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT SomeCol2 
				FROM Test
				WHERE 1 =1'

IF @col IS NOT NULL 
    SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '
    
    
    
EXEC sp_executesql @SQL,N'@InnerParamcol INT',@col

Now let’s run these queries and take a look at the reads

SET STATISTICS IO ON
GO

DECLARE @col INT
SELECT @col = 1

SELECT SomeCol2 
FROM Test
WHERE 1 =1
AND  (SomeCol2=@col OR @col IS NULL)




GO

DECLARE @col INT
SELECT @col = 1

DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT SomeCol2 
				FROM Test
				WHERE 1 =1'

IF @col IS NOT NULL 
    SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '
    
    
    
EXEC sp_executesql @SQL,N'@InnerParamcol INT',@col

SET STATISTICS IO OFF
GO

_(8 row(s) affected)

Table ‘Test’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(8 row(s) affected)

Table ‘Test’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As you can see the dynamic SQL query only uses 2 reads where the other solution uses 6 reads.

Here is an image of the execution plan for both queries.

The execution plan show that the dynamic SQL is using a seek where the other query is using a scan

As you can see, there is a place for dynamic SQL and if you use it correctly you will also get plan reuse, take a look at the post Changing exec to sp_executesql doesn’t provide any benefit if you are not using parameters correctly to find out how to use dynamic SQL correctly

[EDIT]

Someone on twitter suggested to try this query

DECLARE @col INT
SELECT @col = 1
 
SELECT SomeCol2
FROM Test
WHERE 1 =1
AND  SomeCol2 = isnull(@col,SomeCol2)

That query also does an index scan.

Here is the execution plan in text for both of the queries that cause the scan

|–Index Scan(OBJECT:([tempdb].[dbo].[Test].[ix_test]),

WHERE:([tempdb].[dbo].[Test].[Somecol2]=isnull([@col],[tempdb].[dbo].[Test].[Somecol2])))

|–Index Scan(OBJECT:([tempdb].[dbo].[Test].[ix_test]),

WHERE:([tempdb].[dbo].[Test].[Somecol2]=[@col] OR [@col] IS NULL))

There was also a comment about recompiles, when you use sp_executesql you should not get recompiles when changing the value that you are passing in. I ran this query

DECLARE @col INT
SELECT @col = 1
 
DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT SomeCol2
                FROM Test
                WHERE 1 =1'
 
IF @col IS NOT NULL
    SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '
   EXEC SP_EXECUTESQL @SQL,N'@InnerParamcol INT',@col 
   Go
   
      
      
      DECLARE @col INT
SELECT @col = 2
 
DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT SomeCol2
                FROM Test
                WHERE 1 =1'
 
IF @col IS NOT NULL
    SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '
   
   EXEC SP_EXECUTESQL @SQL,N'@InnerParamcol INT',@col 
   Go
   
   
   DECLARE @col INT
SELECT @col = 3
 
DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT SomeCol2
                FROM Test
                WHERE 1 =1'
 
IF @col IS NOT NULL
    SET @SQL = @SQL + ' AND SomeCol2=@InnerParamcol '
   
   EXEC SP_EXECUTESQL @SQL,N'@InnerParamcol INT',@col 
   Go
   

And then I ran a trace checking for SQL:StmtRecompile

Here is the output from that trace

[/EDIT]

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum