The request was aborted: Could not create SSL/TLS secure channel. DNG - Dynamic Where Clause

Skip to: site menu | section menu | main content

DotNetGenerics.com

Anything and everything about Microsoft .NET technology ...
Currently viewing: Skip Navigation LinksDotNetGenerics.com » Tricks & Tips » SQL Server » Dynamic Where Clause

DotNetGenerics.com - Anything and everything about Microsoft .NET technology ...

Menu:

You have not logged in.

Advt:

Links:

Malaika Consultants LLC
Microsoft .NET
ASP.NET
MSDN
Microsoft SQL Server
C#.NET
VB.NET
Visual Studio.NET

Email: Contact Us

Dynamic Where Clause

.NET 2.0

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