Whether to filter before or after... 2

[Amesville]

Hi,

OK so I have a SQL Statement I'm currently passing as a string in a command object from VB 2008 to SQL Server, in which I have a standard query that's in boilerblate text, and I concatenate in a WHERE clause before sending it when I need to filter the returned data set.

I'm thinking of taking this and creating a stored procedure out of it, but I'm not sure how to handle the variable WHERE clause this way. Since the conditions can be changed by the user to filter the data by several different fields and values, I don't want to create an SP for every eventuality (It would be impossible anyway). So I got to thinking, would it be possible to send the WHERE clause as a Parameter when calling the procedure and concatenate it inside the SP similar to the way I do it now?

Or would it just be better to return the whole set and filter it in the DataGridView I'm using to show the results?

Just curious if this can (or should) be done...

Craig

Amesville

 

[gmmastros]

There's a fair amount of debate regarding this type of query.

First, I applaud your desire to use stored procedures. In my opinion, it makes the code a lot easier to maintain. Of course, this is an edge case where you can argue that it's not really easier.

There are various methods you can use to accommodate variable where clauses. At the base of it all, you need to consider [google]SQL Injection[/google]. You could argue that your app is VB and not a web site, so the threat of SQL Injection is minimized, however, if you ever decide to migrate to a web app and you forget about this query, you suddenly have a potential problem.

Basically, SQL Injection is when someone becomes creative with text inputs. For example, suppose you have a text box where you allow someone to type a color. The expected result should be a list of widgets that match the color. so...

[tt]Select Columns
From Table(s)
Where Color = '<the contents of a text box>'[/tt]

Now, suppose someone types this in to the text box:
Blue'; Delete From Widgets Where '1'='1

You query becomes:

[tt]Select Columns
From Table(s)
Where Color = 'Blue'; Delete From Widgets Where '1'='1'[/tt]

Ooooppsss. Time to start looking for your backups. ouch.

You can completely ignore the risk of SQL Injection by creating parameters for your stored procedure, and then writing your query like this:

Create Procedure Search @Color VarChar(20), @Weight Numeric(10,2)
AS
SET NOCOUNT ON

Select Columns
From   Table(s)
Where  (@Color Is NULL Or Color = @Color)
       And (@Weight Is NULL Or Weight = @Weight)

This code only works if you pass NULL in to the stored procedure when the user chooses not to filter on that criteria. For example, if the user doesn't care about the color, you would call the stored procedure like this:

Exec Search NULL, 20.5

If the user doesn't care about weight, but wants blue widgets:

Exec Search 'Blue',NULL

There is a problem with this approach. The problem is... performance. SQL Server cannot effectively use an index to speed up this query.

Your best approach from a performance perspective would be to use dynamic SQL. You would pass each parameter in to the stored procedure and build your where clause dynamically within the stored procedure. Make sure you use sp_executesql and parameters. There are plenty of examples for this if you google for it.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Here are good links on this topic I have:

- short blog by Denis Gobo
[url=http://www.sommarskog.se/dyn-search-2008.html Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)[/url]- long and comprehensive article by Erland Sommarskog
[url=http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries All Queries[/url]- short blog by Gail Shaw
[url=http://aboutsqlserver.com/2010/08/29/sunday-t-sql-tip-how-to-select-data-with-unknown-parameter-set]Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch


PluralSight Learning Library

 

[markros]

Oops, I thought I fixed that before, sorry

Do you use ISNULL(...). Don't, it does not perform- short blog by Denis Gobo
Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)- long and comprehensive article by Erland Sommarskog
Catch All Queries- short blog by Gail Shaw
Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch

------------------------------------------------------------
I need to fix on OneNote in addition to fixing in links.txt

PluralSight Learning Library

 

[Amesville]

Thanks Guys, at least I am asking good questions. George, yes the reason I'm trying to do as much as possible through Stored Procs is because of SQL Injection. Also I'm trying to get familiarized with SQL Server 2008 since I recently lost my job and I'm trying to expand my skills. So far, so good I think.

I will take a good look at the Dynamic Search links above, Mark, thank you. I'm very interested to see how it works.

I have one other question which came up a bit ago - I have a number of scalar text queries that return a single value and not a recordset, either a String or an Integer. I'm not really sure how to structure a stored proc to return a single value and not a set of data, even if it is just one field. Not sure if this is a VB.Net issue of if it's something to look at in SQL Server, or both...

Craig

Amesville

 

[gmmastros]

There's several ways you can handle queries that return a scalar value.

1. You can handle them the same way you do any other query. The only difference is, there's only one column and one row. This is how I usually do it. I prefer this method because it makes all the front end code the same. This method does not perform as well as the other method.

You can easily write a stored procedure that returns a single column and single row. Ex:

Create Procedure HelloWorld_Recordset
AS
  Select 'Hello World' As Result

2. You could write a stored procedure with an output parameter and then use vb.net to capture the output parameter. With this method, there is no recordset, so there is less overhead, and therefore slightly faster. In a very high transaction environment, you would certainly be better off using this method.

Ex:

Create Procedure HelloWorld_Parameter
  @Result VarChar(20) OUT
AS
  Set NoCount On
  Set @Result = 'Hello World'

I have no experience with VB.Net and using output parameters, but a quick google search should help you here: [google]vb.net sql server output parameter[/google]


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Amesville]

Thanks George - I was able to take your example and do some more research, and I found what I needed to make the Output Parameter query work.

Craig

Amesville