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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
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

T-SQL
1
2
3
4
5
6
7
DECLARE @col INT
SELECT @col = 1
 
SELECT SomeCol2 
FROM Test
WHERE 1 =1
AND  (SomeCol2=@col OR @col IS NULL)
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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
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

T-SQL
1
2
3
4
5
6
7
DECLARE @col INT
SELECT @col = 1
 
SELECT SomeCol2
FROM Test
WHERE 1 =1
AND  SomeCol2 = isnull(@col,SomeCol2)
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
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
   
 
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