In an effort to consolidate reporting on our web apps, we normally create a central report setup page that allows a user to select a report and various combinations of filters based on the data in the database. Internally, our best known approach has been to generate a SQL string in the VB behind our ASP.NET pages and send the string in as a hard-coded “Select…” statement via the SQL connection. We’ve always had a feeling, however, that this isn’t the “best” approach, especially when considering how labor intensive it might be to edit code that could be much longer than something like:

dim str as new StringBuilder("")

str.Append("Select col1, col2, col3 From Table1 as t1")

str.Append("inner join Table2 as t2.col1 = t1.col1")

if WhereClause() <> "" then

str.Append(”Where 1=1″)

if checkbox1 = clicked then

str.Append(” and col1 = checkbox1.text”)

elseif checkbox2.checked = true then

str.Append(” and col2 = checkbox2.text”)

end if

end if

Time constraints have prevented us from researching better approaches, but we have always hoped to find a way of using stored procedures, passing in where clauses as parameters when necessary.

Today I stumbled on a better approach while browsing through the questions on StackOverflow.

Caution should be taken when using either of these approaches, especially if the report setup filters include text box entries–your database could be opened up to injection attacks.

I’ll append to this blog as newer methods are discovered.

I’ve found another link:  http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

I’ve also gotten additional responses on Stack Overflow concerning creating a temporary table and performing the filter on the temp table like so:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
Drop table #tmp;
CREATE TABLE #tmp (col1 varchar(10))
INSERT #tmp (col1)
EXEC spTest
declare @spstr as nvarchar(Max)
declare @whereClause as varchar(300)
set @whereClause = 'Where col1 = ''1'''
set @spStr = 'Select * from #tmp ' + @whereClause
exec(@spStr)

One more link:  http://sqlservernation.com/blogs/development/archive/2009/12/07/using-stored-procedures-to-query-objects-with-dynamic-sql.aspx