June 27, 2012 at 9:22 am
In a Stored Proc, can you use a CASE statement to create your SQL ... (the below try errors out on 'Case'
ALTER procedure [dbo].[test]
@model varchar(1000),
@from1 varchar(1000),
@from2 varchar(1000),
@from3 varchar(1000)
AS
declare
@Cmd varchar(1000),
------------------------------------------------------------------------------------- Begin Procedure ---------------------------------------------------------------|
begin
case
when @model = 'Global' then
Set @Cmd ='
SELECT '+@model+' AS Model
, Scenario_ID
FROM lp_Scenario AS a
Else
SET @Cmd = '
SELECT '+@model+' AS Model
, a.Scenario_ID
, a.Last_Gen_DT
FROM '+@from1+' a
INNER JOIN '+@from2+' b
ON a.User_ID = b.User_ID
WHERE (a.Scenario_ID =
(SELECT Scenario_ID
FROM '+@from3+' AS z
WHERE (Auto_Update = 1)))'
End
GO
EXEC (@Cmd)
June 27, 2012 at 9:25 am
No that is not how case works in sql. You need to use an IF - ELSE construct.
And You should read up on sql injection. Your procedure is wide open.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2012 at 9:39 am
That is a simplistic version of the code.. the problem I have with IF statements, is that you have to create a very long IF THEN Else IF... version to deal with all the possible Model's in this case... and it makes reading the code a lot harder. If I have to go that route then I will just create a stored proc for each set of models that use the same select statements.
June 27, 2012 at 10:39 am
I figured it was a stripped down version but yes you have to use if type logic for this. CASE is not used as a control block like a switch statement in c#.
Regardless of which approach you take you seriously need to look at sql injection. The code posted is extremely vulnerable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2012 at 10:56 am
dwilliscp (6/27/2012)
In a Stored Proc, can you use a CASE statement to create your SQL ... (the below try errors out on 'Case'ALTER procedure [dbo].[test]
@model varchar(1000),
@from1 varchar(1000),
@from2 varchar(1000),
@from3 varchar(1000)
AS
declare
@Cmd varchar(1000),
------------------------------------------------------------------------------------- Begin Procedure ---------------------------------------------------------------|
begin
case
when @model = 'Global' then
Set @Cmd ='
SELECT '+@model+' AS Model
, Scenario_ID
FROM lp_Scenario AS a
Else
SET @Cmd = '
SELECT '+@model+' AS Model
, a.Scenario_ID
, a.Last_Gen_DT
FROM '+@from1+' a
INNER JOIN '+@from2+' b
ON a.User_ID = b.User_ID
WHERE (a.Scenario_ID =
(SELECT Scenario_ID
FROM '+@from3+' AS z
WHERE (Auto_Update = 1)))'
End
GO
EXEC (@Cmd)
Very Good and critical point raised by Sean for this piece of code.SQL injection is very much possible here.Don't let user to run their query by your vulnerable code.It can harm you and your company both.
The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 27, 2012 at 11:20 am
I created that set of code, for this example to show what I was trying to do.. not the code that is in the stored proc (it is doing a lot more that this simple creation does).. my point was just to show what I wanted to do... and that was to use the case statement to create the SQL statement depending on the Model being passed into the code. In fact the Stored Proc can only be run by the Admin, and is used to load data during nightly processing.
June 27, 2012 at 7:08 pm
dwilliscp (6/27/2012)
That is a simplistic version of the code.. the problem I have with IF statements, is that you have to create a very long IF THEN Else IF... version to deal with all the possible Model's in this case... and it makes reading the code a lot harder. If I have to go that route then I will just create a stored proc for each set of models that use the same select statements.
The version using IF looks nearly identical to me.
--case when
IF @model = 'Global' -- then
Set @Cmd ='
SELECT '+@model+' AS Model
, Scenario_ID
FROM lp_Scenario AS a'
-- Second THEN of CASE
-- ELSE IF @model = 'Something' -- Uses ELSE IF instead of WHEN/THEN
-- Then
Else
SET @Cmd = '
SELECT '+@model+' AS Model
, a.Scenario_ID
, a.Last_Gen_DT
FROM '+@from1+' a
INNER JOIN '+@from2+' b
ON a.User_ID = b.User_ID
WHERE (a.Scenario_ID =
(SELECT Scenario_ID
FROM '+@from3+' AS z
WHERE (Auto_Update = 1)))'
--End
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 27, 2012 at 7:18 pm
But if you're wedded to CASE you can do it like this:
SELECT @CMD=
case
when @model = 'Global' then
'SELECT '+@model+' AS Model
, Scenario_ID
FROM lp_Scenario AS a'
Else
'SELECT '+@model+' AS Model
, a.Scenario_ID
, a.Last_Gen_DT
FROM '+@from1+' a
INNER JOIN '+@from2+' b
ON a.User_ID = b.User_ID
WHERE (a.Scenario_ID =
(SELECT Scenario_ID
FROM '+@from3+' AS z
WHERE (Auto_Update = 1)))'
End
SELECT @CMD
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 27, 2012 at 10:55 pm
dwain.c (6/27/2012)
But if you're wedded to CASE you can do it like this:
SELECT @CMD=
case
when @model = 'Global' then
'SELECT '+@model+' AS Model
, Scenario_ID
FROM lp_Scenario AS a'
Else
'SELECT '+@model+' AS Model
, a.Scenario_ID
, a.Last_Gen_DT
FROM '+@from1+' a
INNER JOIN '+@from2+' b
ON a.User_ID = b.User_ID
WHERE (a.Scenario_ID =
(SELECT Scenario_ID
FROM '+@from3+' AS z
WHERE (Auto_Update = 1)))'
End
SELECT @CMD
Hey Dwain,
Finally you make dwilliscp's day 😉
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 27, 2012 at 11:14 pm
Finally? I didn't realize I was on a deadline...:w00t:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 28, 2012 at 12:04 am
Hey all, have a look at these two articles.
http://www.sqlservercentral.com/articles/Performance+Tuning/howdynamiccanstaticbe/631/
and http://www.sqlservercentral.com/articles/Performance+Tuning/howdynamicsqlcanbestatic2/684/
An nice fancy way of building SQL using case statements and it covers select, where, joins and other niceties. Of course it won't perform well as plan generation is difficult but the concept is nice and fancy.
July 2, 2012 at 8:50 am
.... No loops! No CURSORs! No RBAR! Hoo-uh! ...
Great!
Thanks rhythmk and dwain.c for your help...
The IF statement would have 5 nested IF's... and then the else.. I know it is just my preference.. but if you can not get it done in a If Then Else.. then I like to use Case. Doing less C coding in this new position and a lot more SQL... no Pick though, so I guess it is a good trade off. :hehe:
Again thanks for your help!
July 2, 2012 at 8:53 am
I headed out for vacation last week, only to end up with no power... after the big storm moved through the Smokey Mountains Friday... so I am back early 🙁
July 2, 2012 at 9:01 am
You will find far better performance and code maintenance by creating a sproc for each of your different select statements. The code dwain posted is indeed quite a clever way of making case somewhat function like a programming block. The downside is the performance of that is going to suffer greatly and tracking it down will become painful. Not to mention that code will be a lot harder to maintain because dynamic sql is just harder to read when it gets complicated like that. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2012 at 9:06 am
Sean Lange (7/2/2012)
You will find far better performance and code maintenance by creating a sproc for each of your different select statements. The code dwain posted is indeed quite a clever way of making case somewhat function like a programming block. The downside is the performance of that is going to suffer greatly and tracking it down will become painful. Not to mention that code will be a lot harder to maintain because dynamic sql is just harder to read when it gets complicated like that. Just my 2¢.
Are you sure, Sean? I totally agree with debugging and maintenance being a pain in the a$$ but I'm not so sure about performance.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply