James Dingle,

In fact your query preforms nearly twice as badly as the original non-dynamic SQL that would have been in the initial stored procedure.

See SQL and execution plan below.

DECLARE @Name nvarchar(100),

@Color nvarchar(100),

@ListPriceMax money,

@ListPriceMin money,

@ModifiedDateMin datetime,

@ModifiedDateMax datetime

SET @Name = 'Hello'

SET @Color = NULL

SET @ListPriceMax = NULL

SET @ListPriceMin = NULL

SET @ModifiedDateMin = NULL

SET @ModifiedDateMax = NULL

--Query 1

SELECT *

FROM [Product]

WHERE [Name] = Coalesce(@Name, [Name])

AND [Color] LIKE Coalesce(@Color, [Color] )

AND [ListPrice] >= Coalesce(@ListPriceMin, [ListPrice])

AND [ListPrice] <= Coalesce(@ListPriceMax, [ListPrice])

AND ModifiedDate >= Coalesce(@ModifiedDateMin, ModifiedDate)

AND ModifiedDate <= Coalesce(@ModifiedDateMax, ModifiedDate)

--Query 2

;

WITH Filtering AS

(

SELECT ProductId

FROM [Product]

WHERE @Name IS NOT NULL AND @Name = Name

UNION ALL

SELECT ProductId

FROM [Product]

WHERE @Color IS NOT NULL AND @Color = Color

UNION ALL

SELECT ProductId

FROM [Product]

WHERE NOT (@ListPriceMin IS NULL AND @ListPriceMax IS NULL)

AND (@ListPriceMin IS NOT NULL OR ListPrice >= @ListPriceMin)

AND (@ListPriceMax IS NOT NULL OR ListPrice <= @ListPriceMax)

)

SELECT *

FROM [Product]

WHERE ProductID IN (SELECT ProductID

FROM Filtering)

AND [Name] = Coalesce(@Name, [Name])

AND [Color] LIKE Coalesce(@Color, [Color] )

AND [ListPrice] >= Coalesce(@ListPriceMin, [ListPrice])

AND [ListPrice] <= Coalesce(@ListPriceMax, [ListPrice])

AND ModifiedDate >= Coalesce(@ModifiedDateMin, ModifiedDate)

AND ModifiedDate <= Coalesce(@ModifiedDateMax, ModifiedDate)

Execution plans:

Query 1 uses 35% of total cost and query 2 uses 65% of total cost.