Dynamic Where Clause
Do you want to use a dynamic WHERE clause in a stored procedure based on an input parameter but don't want to use dynamic sql?
We come across the situation where we have a WHERE clause of a SELECT query dynamically built on an input parameter. Suppose you have a Authors table which has fields au_id and lname etc.
Now if need to filter the table based on mulitple au_id s -- common scenario being user able to select which authors they want the data for, from a multi-select list box containing the au_id s.
Usually we will either build the dynamic query in the business logic layer based on the au_id input lie this in C#:
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append("SELECT * FROM authors WITH (NOLOCK) WHERE 1=1 AND ");
//Then loop through the select box items to get the selected ids and create the WHERE clause
for (...)
{
sql.AppendFormat(" au_id = '{0}', selectedId);
}
//then execute the dynamic sql
But what if we need to have a stored procedure rather than a dynamic query ? That way we can have all the advantages of the stored procedure (prepared execution plan, pre-compiled)
We could create the dynamic sql in the stored procedure and then execute it , however this will NOT have a prepared execution plan. We can probably send the au_id s as a comma-separated input
and then split it in the stored procedure -- not very clean though. So how do we achieve dynamic sql in the WHERE clause for a stored procedure ?
XML to the rescue !
Yep you heard it right. I never thought XML would help in such a situation either, but it does. Let us see how.We will be using the pubs database that is shipped by Microsoft with SQL server.
Let us declare a variable for the filter (This can be set to NTEXT if using in a Stored procedure)
DECLARE @filter NVARCHAR(4000)
Now this @filter variable will have the au_ids in XML schema as follows (you can use your own schema):
<?xml version="1.0"?>
<Filter>
<Author><au_id>1</au_id></Author>
<Author><au_id>2</au_id></Author>
</Filter>
Now convert this xml to be inserted in a table variable :
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @filter
DECLARE @authors_filter TABLE (au_id VARCHAR(20))
INSERT INTO @authors_filter
SELECT au_id FROM Openxml( @docHandle, '/Filter/Author', 3) WITH (au_id VARCHAR(20))
At this point our @authors_filter table variable has all the ids that we want to filter on
Now simply get the filtered result
SELECT * FROM authors A WITH (NOLOCK) INNER JOIN @authors_filter AF ON (A.au_id = AF.au_id)
That is it !! Download the code here
Comments:
brunopiovan @ 4/24/2006 9:49:38 PM
Very good!
Login to add your comments