Binaryworld - How to create dynamic query without loosing performance. ... [ SQL Server -> Stored Procedure ]

We all know that in sql server you can use exec or execute statement to execute dynamic sql statement which can be on the fly but the biggest drawback of executing dynamic query using execute is query plan is not chached by sql server and so you get poor performance if you hitting many tables and several records.

Most of people build dynamic query as shown below which is easier to write but performance will be worst.

Click here to copy the following block

Declare @sql varchar(8000)
Declare @filter varchar(8000)

@sql='select * from orderdetails where 1=1 '

if @productid is not null then
set @filter=@filter + ' AND productid=' + @someproductid

if @categoryid is not null then
set @filter=@filter + ' AND categoryid=' + @somecategoryid

. . .

. . .

execute(@sql + @filter)

Dynamic query sud be the last option if you looking for the best performance. Ok then whats the solution ??? How can I inplement dynamic sql statement ??? .....

Here is the simple technique which can be used when you need to build dynamic query based or some parameters. This query uses COALESCE function. COALESCE will return first non null value from the arguments. You can also use CASE statement if you want but I find COALESCE easier to read and implement. Since we are not using any dynamic sql statement this entire stored proc will be compiled first time and stored in cache so next time when you call the same stored proc sql server will reuse the plan and cached data.

Click here to copy the following block

USE Northwind

go

/*

Description : Search Orders by Product, Category and Customer

if Product is not passed then no filer for Product will be applied
if Category is not passed then no filer for Category will be applied
if Customer is not passed then no filer for Customer will be applied

example-1 (Show all order items for all products, all categories , all suppliers and all customrs)
=========================================================
exec usp_SearchOrderItems

example-2 (Show all order items where product name like [Mix])
=========================================================
exec usp_SearchOrderItems @ProductName='%Mix%'

example-3 (Show all order items where product name like [Mix]) and Category is [Condiments]
=========================================================
exec usp_SearchOrderItems @ProductName='%Mix%', @Categoryname='Condiments'

example-4 (Show all order items where product name like [Mix]) and Category is [Condiments] and customerid=[ERNSH]
=========================================================
exec usp_SearchOrderItems @ProductName='%Mix%', @Categoryname='Condiments', @CustomerId='ERNSH'

select * from orders
select * from [order details]
select * from products
*/
Alter PROCEDURE usp_SearchOrderItems
@ProductName varchar(50) = NULL,
@CategoryName varchar(50) = NULL,
@CustomerId varchar(50) = NULL
AS

select o.OrderId,o.OrderDate,p.ProductName,c.Categoryname,cu.CustomerId from [order details] od
join orders o
on o.orderid=od.orderid
join Products p
on p.Productid=od.ProductId
join Categories c
on c.categoryid=p.categoryid
join Customers cu
on o.CustomerId=cu.Customerid
WHERE p.ProductName LIKE (COALESCE(@ProductName,p.ProductName)) AND
c.CategoryName LIKE (COALESCE(@CategoryName,c.CategoryName)) AND
cu.CustomerId = (COALESCE(@CustomerId,cu.CustomerId))
Order by p.ProductName,c.CategoryName,cu.CustomerId

go