Populating @Variables dynamically
-
I have an existing proc I need to optimize. It has some redundancy in its variable population:
SELECT @code1type = CASE
WHEN UPPER( @code1TBL) = 'TB_MACS' THEN " AND UPPER( type) LIKE 'OBJ%'"
WHEN UPPER( @code1TBL) = 'TB_TRNM' THEN " AND UPPER( type) ='TRNM'"
WHEN UPPER( @code1TBL) = 'TB_SIC' THEN " AND UPPER( type) ='SIC'"
WHEN UPPER( @code1TBL) = 'TB_SPG' THEN " AND UPPER( type) ='SPG'"
WHEN UPPER( @code1TBL) = 'TB_MSON' THEN " AND UPPER( type) ='MSON'"
WHEN UPPER( @code1TBL) = 'TB_PLAN' THEN " AND UPPER( type) ='OAR'"
WHEN UPPER( @code1TBL) = 'TB_EAR' THEN " AND UPPER( type) ='EAR'"
ELSE ''
END
SELECT @code2type = CASE
WHEN UPPER( @code2TBL) = 'TB_MACS' THEN " AND UPPER( type) LIKE 'OBJ%'"
WHEN UPPER( @code2TBL) = 'TB_TRNM' THEN " AND UPPER( type) ='TRNM'"
WHEN UPPER( @code2TBL) = 'TB_SIC' THEN " AND UPPER( type) ='SIC'"
WHEN UPPER( @code2TBL) = 'TB_SPG' THEN " AND UPPER( type) ='SPG'"
WHEN UPPER( @code2TBL) = 'TB_MSON' THEN " AND UPPER( type) ='MSON'"
WHEN UPPER( @code2TBL) = 'TB_PLAN' THEN " AND UPPER( type) ='OAR'"
WHEN UPPER( @code2TBL) = 'TB_EAR' THEN " AND UPPER( type) ='EAR'"
ELSE ''
END
and so on through to @code7type. I should be able to exec strings to increment the @Variable, (i.e., '@code' + @IncrementValue + 'type'), but that will spawn another SPID and I am wondering if it will have difficulty when it returns to the original SPID in populating these @Variables.
Has anyone done something like this before?
Thanks
I wasn't born stupid - I had to study.
-
Hi Farrel,
I take it you are trying to do something like:-
Declare @Variable1 varchar, @Variable2 varchar, @Variable3 varchar, @Variable4 varchar, @Counter int
Then step through the variables using something like:
FOR @Counter = 1 TO 3
SELECT * FROM ATABLE WHERE ACOLUMN = (@Variable + @Counter)
Next
Steve
We need men who can dream of things that never were.
-
Thank you Steve.
Sort of... Basically, the variables are populated outside the stored procedure (seven possible strings are put into the @Variables). Because this system has changed over time, they will need to be re-assigned. Rather than write the same code 7 times for the 7 @Variables, I want to loop.
I can do it by assigning one variable with a counter and adding the new number from the counter every time to the string containing the variable name:
For @Counter 1 to 7
@NewVariable = '@Variable' + @Counter + 'end of variable name'
@sql = 'code the CASE statements'
EXEC @sql
Next
These @Variables need to be populated for use later in the Stored Procedure. I cannot change the front end, so I need to increase the efficiency of the existing code.
I know how to do it that way, what concerns me is dynamic SQL will spawn a new SPID and I am not sure that my @Variables will retain the correct information due to this... If the original stored procedure is SPID 1, this Loop will spawn a SPID 2 - 8 and bring back that information into SPID 1. Will SPID 1 truly retain the correct values for these @Variables?
If you have, (or someone else) has done this before, I would very much appreciate the advice. I am trying not to reinvent the wheel and I would imagine this is a common practice, but just not one I have written. (Gosh I hope I explained this better...)
(I wasn't born stupid, I had to study...)
I wasn't born stupid - I had to study.
-
dynamic ? Think #/## temptb or @ tablevar.
Populate your temp-object with search- and replacementvalues and join it so you get the correct results.
Maybe this gets you on track:
http://www.sqlservercentral.com/scripts/contributions/100.asp
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32921&SearchTerms=CSV
http://www.sqlservercentral.com/columnists/rmarda/dynamicvsstatic2.asp
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi GrassHopper,
I must admit I have used this form of sql a lot just recently - dangerous saying Dynamic in these posts, it becomes a massive discussion on the subject and you lose track of the original point.......
In the same sproc, i've dropped triggers and tables, recreated the tables and recreated the triggers - all based around an input variable of name and a couple of internal variables which were manipulated to suit the next declaration.
I did know that it generated a new SPID but hadn't really seen it as a potential issue. I suppose this is fortunate for me, because I just went ahead and wrote it without worrying.
Even more fortunately, I have never had any problems with any of the variables losing their original / new values. They always perform as expected.
So, from personal experience, I don't see it as an issue and - as long as the SPIDs arent used within the sproc/s for anything else - wouldn't expect any problems.
Did I really say that - thats inviting trouble......... 
Good Luck, Have Fun and Keep us posted on your progress mate.
Best regards
Steve
Steve
We need men who can dream of things that never were.
-
Ferrell,
When you think about it, your case statement is simply another array of values. This could also be represented in a table, and could be processed much more efficiently. When I do build "Dynamic" code (which I usually only use to update/create procedures and other database objects) I make the elements of the query based on tables.
cl
Signature is NULL
-
Thanks. I have some time on this one and these suggestions will really help me plan out my approach - I imagine I will find a number of sprocs with this type of methodolgy which can be tweaked.
I wasn't born stupid - I had to study.
-
Not having very good luck with this. The first step in this existing process is to rename variables passed into the Stored Procedure.
I was having a great deal of difficulty with this, (i.e., getting a must DECLARE @variable errors), so I made local @variables and populated them with the information passed into this Stored Procedure, but not luck.
I like the idea of populating tables (I too have only used dynamic SQL for table manipulation), but I need these @variables for more dynamic SQL later in the program. I have copied a brief snippet of the code below in case that helps anyone determine what I need to do differently....
SELECT @Counter = 1
WHILE @Counter <= 6
BEGIN
SELECT @sql = ' SELECT @code' + CONVERT( varchar(1), @Counter) + 'TBL = (' +
' SELECT CASE ' +
' WHEN UPPER( @code' + CONVERT( varchar(1), @Counter) + 'TBL ) = ' + CHAR(39) + 'TB_MACS' + CHAR(39) +
' THEN ' + CHAR(39) + 'tb_bad_cte' + CHAR(39) +
' END )'
-- PRINT @sql
EXEC( @sql)
SELECT @Counter = @Counter + 1
END
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@code1TBL'.
This error proceeds through @code6TBL. I have tried numerous machinations (sp) of putting CHAR(39) [single quote] into the code with no luck.
Guess I will keep thinking about it and looking to see if anyone has replied.
Thanks.
I wasn't born stupid - I had to study.
-
The nice thing about "dynamic" is that it has issues
Because "exec (@sql)" lives its own life, you have to incorporate the declares.
i.e.
SELECT @Counter = 1
WHILE @Counter <= 6
BEGIN
SELECT @sql = ' declare @code' + CONVERT( varchar(1), @Counter) + 'TBL as varchar(128) '
+ char(13) + ' SELECT @code' + CONVERT( varchar(1), @Counter) + 'TBL = (' +
' SELECT CASE ' +
' WHEN UPPER( @code' + CONVERT( varchar(1), @Counter) + 'TBL ) = ' + CHAR(39) + 'TB_MACS' + CHAR(39) +
' THEN ' + CHAR(39) + 'tb_bad_cte' + CHAR(39) +
' END )'
-- PRINT @sql
EXEC( @sql)
SELECT @Counter = @Counter + 1
END
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
You may also want to read this nice page http://www.sommarskog.se/dynamic_sql.html entitled 'The Curse and Blessings of Dynamic SQL' - if for nothing else to verify that what you're doing is justified.
/Kenneth
-
Hi Again,
Sorry - only just got the e-mail. You are both actually right in different ways.
Farrel - You are getting the insertion of the variables into your sequel string wrong. If you expect to pass a parameter to the sequel string you need to do it like:
SET @sql = 'SELECT something FROM ' + @TABLENAME + ' WHERE somecondition = ' @CONDITION
So the last piece of SQL you posted with errors, should read:
DECLARE @Counter Int,
@Code varchar(250),
@SQL varchar(8000)
SELECT @Counter = 1
WHILE @Counter <= 6
BEGIN
SET @Code = 'SomeThing'
SET @sql = 'SELECT ' + @code + CAST(@Counter AS varchar(10)) + ' TBL = (' +
' SELECT CASE ' +
' WHEN UPPER(' + @code + CAST(@Counter AS varchar(10)) + ' TBL ) = TB_MACS' +
' THEN tb_bad_cte' +
' END )'
PRINT @sql
--EXEC( @sql)
SELECT @Counter = @Counter + 1
END
Which will give you an @sql of : "SELECT SomeThing6 TBL = ( SELECT CASE WHEN UPPER(SomeThing6 TBL ) = TB_MACS THEN tb_bad_cte END )"
You can use variables just in the scope of the EXEC but it's more complicated and harder to do. I would recommend getting the insertion right and fully understanding this process first...
Other recommended reading:
http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx
http://weblogs.asp.net/fbouma/archive/2003/06/16/8731.aspx
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
Have fun
Steve
We need men who can dream of things that never were.
-
Farrel,
Sorry - missed the bit about single quotes. Its easy to use this method:
DECLARE @Counter Int,
@Code varchar(250),
@SQL varchar(8000)
SELECT @Counter = 1
WHILE @Counter <= 6
BEGIN
SET @Code = 'SomeThing'
SET @sql = 'SELECT ' + @code + CAST(@Counter AS varchar(10)) + ' TBL = (' +
' SELECT CASE ' +
' WHEN UPPER(' + @code + CAST(@Counter AS varchar(10)) + ' TBL ) = ''TB_MACS''' +
' THEN ''tb_bad_cte''' +
' END )'
PRINT @sql
--EXEC( @sql)
SELECT @Counter = @Counter + 1
END
Now the string result is:
"SELECT SomeThing6 TBL = ( SELECT CASE WHEN UPPER(SomeThing6 TBL ) = 'TB_MACS' THEN 'tb_bad_cte' END )"
Have fun
Steve
We need men who can dream of things that never were.
-
Thank you all!! My horoscope said today would be good. I have a number of things to try.
I know the table method and that will work for this first section; I have not gone into the second section which is much more intensive
( I will be pushing the limits of varchar(8000)) and will become extremely complicated if I need to use tables. I will keep that option available though... Thank you alzdba for your pointing out the DECLARE within the scope of the Dynamic SQL SPID. I will look into that today. (I also appreciated seeing your use of CHAR(13) - I thought I was one of the few who used that - it does print out nicely to see just what the heck is going on...).
Wish me luck! And again, thank you all for such thorough reviews!!
I wasn't born stupid - I had to study.
-
Farrel,
You may find this useful if you are going to push it further.
If you need to return values from dynamic sql within the same sproc a handy bit of code is:-
DECLARE @sql nvarchar(4000),
@ColumnName sysname,
@A_Variable int
SET @sql = N'SET @A_Variable = ((SELECT ISNULL(MAX(RowKey), 0) FROM ' + @ColumnName + ') + 1)'
EXEC sp_executesql @sql, N'@A_Variable int OUTPUT', @A_Variable OUTPUT
@A_Variable will have the value from your SELECT statement.
Keep us posted on your code, there isn't enough in these forums on dynamic sql.......
Guilty of editing this post to put the sql right
Have fun
Steve
We need men who can dream of things that never were.
-
Thanks Steve! Kenneth Wilhelmsson gave me URL to a great article on this using sp_executesql - I love it!
Hopefully today I will be the weasel!! (I can't believe I just said that...)
I wasn't born stupid - I had to study.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply