March 30, 2010 at 2:46 pm
Hi,
Is it possible to have dynamic sql inside a stored procedure? I have a rather long query, and right now I have 3 different versions of the query in my stored procedure. The value of a variable, determines which query is used. I would like to shorten the stored procedure and be able to use just one query, but have it include or exclude various AND clauses depending on the value of the variable.
Is this possible?
Thanks!
March 30, 2010 at 2:51 pm
The simple answer is yes, you can.
The other choice you have is write the three queries in their own stored proc, and use the master proc to determine which one is called. Personally, I'd probably go this route instead of the dynamic sql.
March 30, 2010 at 2:55 pm
Yes it is. Beware SQL injection though.
These may be worth reading, just in general, if I understand what you're trying to do.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
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
March 30, 2010 at 3:01 pm
From the sound of it you could probably use Case statements. Here's an example:
Declare @var int --decides which parts of where clause to use
,@var2 varchar(20)
,@var3 varchar(20)
Select * From SomeTable
Where Case When @var = 1 then 1 --don't check @var2
Else Case when @var2 = col2 then 1 Else 0 End
End = 1
And Case When @var = 2 then 1 --don't check @var3
Else Case when @var3 = col3 then 1 Else 0 End
End = 1
March 30, 2010 at 3:03 pm
If you have three distinct queries that are run, code each in its own stored procedure and use a master stored procedure to determine which of the three procedures is called.
March 30, 2010 at 3:11 pm
Beedle (3/30/2010)
From the sound of it you could probably use Case statements.
You can, providing performance is not a major requirement.
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
March 30, 2010 at 6:23 pm
The queries are all the same except for various AND statements.
So I guess I could do this?
SELECT * FROM myTable
WHERE 1 = 1
select case @myVar
WHEN 1 THEN
AND myDate > 1/1/2010
WHEN 2 THEN
AND amount < 1000
WHEN 3 THEN
AND amount > 1000
AND days < 50
ELSE
AND catalog = 2
March 30, 2010 at 6:49 pm
Magy (3/30/2010)
The queries are all the same except for various AND statements.So I guess I could do this?
SELECT * FROM myTable
WHERE 1 = 1
select case @myVar
WHEN 1 THEN
AND myDate > 1/1/2010
WHEN 2 THEN
AND amount < 1000
WHEN 3 THEN
AND amount > 1000
AND days < 50
ELSE
AND catalog = 2
This will give you two result sets. Also, you might as well eliminate the where clause in this scenario - or find something to use in the cause to improve your query performance.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 8:34 am
Oops...I definately don't want 2 resultsets!
So I would rewrite it like this
SELECT * FROM myTable
Where Case
WHEN @myVar = 1 THEN
AND myDate > 1/1/2010
WHEN @myVar = 2 THEN
AND amount < 1000
WHEN @myVar = 3 THEN
AND amount > 1000
AND days < 50
ELSE
AND catalog = 2
March 31, 2010 at 9:00 am
DECLARE @myVar int
SELECT * FROM myTable
Where Case
WHEN @myVar = 1 THEN
AND myDate > 1/1/2010
WHEN @myVar = 2 THEN
AND amount < 1000
WHEN @myVar = 3 THEN
AND amount > 1000
AND days < 50
ELSE
AND catalog = 2
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AND'.
You can't use the case statement like that. It can't be used with pieces of the query, only as an expression.
This is valid
SELECT * FROM MyTable
WHERE
Case @MyVar
WHEN 1 THEN Column1
WHEN 2 THEN Column2
ELSE Column3
END = 'SomeValue'
This is also valid
SELECT * FROM MyTable
WHERE SomeCol = Case @MyVar
WHEN 1 THEN 'Value1'
WHEN 2 THEN 'Value2'
ELSE 'Value3'
END
However you cannot have a case around pieces of the where clause. That requires either multiple queries (preferably in multiple procedures) as we have been advocating, or 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
March 31, 2010 at 9:27 am
GilaMonster (3/30/2010)
Beedle (3/30/2010)
From the sound of it you could probably use Case statements.You can, providing performance is not a major requirement.
If you have three distinct queries that are run, code each in its own stored procedure and use a master stored procedure to determine which of the three procedures is called.
Basically, you have three choices.
1. Use a case statement in the where clause. As Gail mentioned, you will have performance issues. See the links she suggests for catch-all queries and multiple-execution-paths... it just can't be described any better than what she has already done in those links.
2. Separate stored procedures, with a master procedure determining which is called. This avoids all of the issues from #1, but adds one: identical logic in multiple procedures, where a change to the logic requires changes in multiple procedures.
3. Dynamic sql. This avoids issues with #1 and #2, but adds the risk of sql injection. Also, if you don't parameterize the query, but instead have the text of the parameters built into the query, you will end up with sql needing to build a new plan for the query every time it is run, which can cause overall server performance issues.
My recommendation: if the number of paths is relatively low (I use 10 or lower), I would go with #2. If > 10, I'd use #3, with a parameterized query to be able to utilize plan re-use. Yes, I would write up to 10 mostly identical procedures (plus the master), purely for overall performance reasons. I would also document inside each of them all of the other procedures that utilize the identical logic, with the note that all would need changing if the logic is changed.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 31, 2010 at 11:51 am
The Case statement in the where clause could cause performance issues if you're dealing with larger databases, I've been able to get away with using it more often than not however. Here's how you'd code it based on your example:
DECLARE @myVar int
SELECT * FROM myTable
Where Case WHEN @myVar = 1 THEN
Case When myDate > '1/1/2010' Then 1 Else 0 End
WHEN @myVar = 2 THEN
Case when amount < 1000 Then 1 Else 0 End
WHEN @myVar = 3 THEN
Case When amount > 1000 And days < 50 then 1 Else 0 End
ELSE
Case When catalog = 2 Then 1 Else 0 End
End = 1
You could also potentially wrap this logic in a bunch of or's, but I think this will give better performance based on tests of similar cases I've done in the past.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply