November 19, 2008 at 12:46 pm
I'm looking for a little guidance on the best (most proper) way to call a stored procedure that would execute a SELECT statement based on a variety of potential variables.
In the past, I built my SQL statements in code and executed the code directly in-line (I know it's WRONG!), so now I am trying to port my SQL calls into stored procedures. I understand how easy it is if you have fixed parameters (i.e. @ID = 1) but how do I send in variable parameters such as:
State in ('MA', 'NY', 'RI')... May be anywhere from zero to 51 selected states
ZipCode = 01234 - May or may not exist
ZipCode 55555 - Range May or May not exist
Type in ('1', '2')... May be anywhere from zero to 20 selected Types
As I mentioned, I have always built the SQL dynamically in code usign stringbuilder functions and was hoping to send a single "querystring" to SQL and have it executed but have not discovered how to do this.
I'm guessing the is SQL 101 basic stuff, but I would appreciate any guidance that you experts can provide.
Many thanks,
Tom
November 19, 2008 at 2:00 pm
There's no direct way. You can use dynamic SQL and concatenate the where clause to the end of the string and execute that, but you open the system to sql injection (google if you're not familiar with it)
What I generally suggest is to build up a dynamic string based on parameters passed to the stored proc and then execute that, taking care that the parameters are never concatenated into the string. There are downsides to this though.
Something like this (based off the adventureworks DB)
DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = ''
SET @sSQL = 'SELECT COUNT(*) from Production.TransactionHistory '
IF @product is not null
SET @Where = @Where + 'AND ProductID = @product '
IF @OrderID is not null
SET @Where = @Where + 'AND ReferenceOrderID = @OrderID '
IF @TransactionType IS NOT NULL
SET @Where = @Where + 'AND TransactionType = @TransactionType '
IF @Qty IS NOT NULL
SET @Where = @Where + 'AND Quantity = @Qty '
IF LEN(@Where) > 0
SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)
EXEC sp_executesql @sSQL, N'@Product int, @OrderID int, @TransactionType char(1), @Qty int',
@product = @product, @OrderID = @OrderID, @TransactionType = @TransactionType, @Qty = @Qty
Can you explain a bit more about the system? What kind and how many types of where clauses are you expecting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2008 at 2:27 pm
The application front-end is a query-builder tool. For example, think of shopping for a car. You may only know that you want a "white" car. However, you may know that you are lookign for a "white" or "blue" car that may be a "ford" and that may be available in a specific zip code (i.e. 12345).
For example the statement could range from:
SELECT * FROM Cars WHERE Color = ‘WHITE'
or it may be more complicated...
SELECT * FROM Cars WHERE Color in (‘WHITE', ‘BLUE’,’RED’…) AND Cylinders = ‘6’ AND ManufactureDate =’20080701’
In the past, I always built the complex SQL WHERE clause in the C# codebase. I was hoping to construct the WHERE clause in C# and send it as a single parameter and that does not appear to work.
Bummer.
November 19, 2008 at 4:12 pm
tom (11/19/2008)
I was hoping to construct the WHERE clause in C# and send it as a single parameter and that does not appear to work.
no, it won't. SQL won't allow entire pieces of the select to be parameters, only places where a variable is allowed. If you want as much freedom as it seems, you'll be using dynamic SQL somewhere, whether it's in the procedure or in the front end. Much the same problems and risks exist no matter where you put the dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply