Populating @Variables dynamically

• Ste_P

  SSCarpal Tunnel

  Points: 4430

  More actions

  November 10, 2004 at 8:14 am

  #529629

  Farrel,

  No worries mate.

  Don't forget though: If you declare the variable within an sql string and exec it, it will generate a new spid and the variable will only have the lifespan of the new spid. It won't be useable anywhere else in your sproc - thats when you get the variable declaration error messages. The only way I have found to return a value is with the sp_executesql. (Quite open to anyone who has found other ways forward posting replies......)

  Thought: Do we need to study to be the Weasel?

   

  Steve

  We need men who can dream of things that never were.

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  November 10, 2004 at 8:26 am

  #529632

  Someone said;

  "Keep us posted on your code, there isn't enough in these forums on dynamic sql......."

  I beg to disagree - it's way way too many questions on dynamic sql in these forums.

  Why? Simply put because dynamic sql should not be the first choice in how to solve a problem, and secondly because it carries with it very far-reaching consequences on.... well, just about everything.

  I would even reach as far as to say, that in general, dynamic sql is 'bad' - don't do it. Having said that, though, there are exceptions of course. However, those exceptions should be very rare indeed.

  Anyway, just my .02

  /Kenneth

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  November 10, 2004 at 1:51 pm

  #529693

  I cannot seem to escape the requirement to DECLARE @Variable1Name, etc... 

  I even tried changing everything to a @@Global, but once a new SPID is rattled off with Dynamic SQL, it cannot seem to find the previous @Variable and reset it. 

  I am going to pursue buffering this information up into #Temp tables and change it there. 

  Thanks for your help!  This has been a real killer for me...

   

  I wasn't born stupid - I had to study.

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  November 11, 2004 at 2:23 am

  #529734

  You can't declare your own global variables in Transact-SQL.

  If you do;

  DECLARE @global - you get a local variable named 'global'..

  If you do;

  DECLARE @@global - you get a local variable named '@global'

  The extra @ only affects the name of the variable, not the type - it's always local when user declared.

  /Kenneth

• Ste_P

  SSCarpal Tunnel

  Points: 4430

  More actions

  November 11, 2004 at 2:36 am

  #529740

  Farrel,

  I hate to admit it but there was a minor error in the last bit of code I posted for you.

  sp_executesql requires that the @sql variable that holds the command string be declared as either ntext/nchar/nvarchar. Run it with nvarchar(4000) and you won't have the same problems.

  Have tested re-setting the variable afterwards and it all works fine.

  DECLARE @sql nvarchar(4000),

   @ColumnName sysname,

   @A_Variable int

   SET @A_Variable = 10

   SET @ColumnName = 'AColumnName'

  PRINT '1 - ' + CAST(@A_Variable as varchar(50))

   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

  PRINT '2 - ' + CAST(@A_Variable as varchar(50))

   SET @A_Variable = 20

  PRINT '3 - ' + CAST(@A_Variable as varchar(50))

  Have fun

   

  Steve

  We need men who can dream of things that never were.

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  November 12, 2004 at 9:58 am

  #529968

  Thank you guys!  I was able to use your code in one location, Steve.  I am grateful for your research. 

  I also very much appreciate knowing that @@Global does not necessarily constitute a Global Variable.  I am stunned by this piece of information.  I had always believed that to be the case.  Was it true at some time?  I seem to remember needing to use a @@Global variable somewhere in another lifetime and it worked.  That is truly a shame as it would have truly helped with scoping issues...

  What is the bugger is, the variables I am using are not static, (like @A_Variable).  I am populating @A_Variable, @B_Variable, etc.  That is where I lose the scope of the Declared @Variable.  Even if I use a static variable and then run SET @B_Variable = @A_Variable, I have to run that statement as Dynamic SQL and once the new SPID is generated, @B_Variable is no longer available.  aaarrggghhhh....  

  Well, I used the advice as viewing this as an array or even simpler a table with one record. 

  I have it working, but it is no where near as clean as I would have hoped.  As has been mentioned numerous times, Dynamic SQL is not necessarily always the answer..., it is often better to ask the question differently...  (I think it is way cool, though...    ) 

  Again, thank you all for your quick and insightful answers...

   

  I wasn't born stupid - I had to study.

• Ste_P

  SSCarpal Tunnel

  Points: 4430

  More actions

  November 12, 2004 at 10:30 am

  #529973

  Hi Farrel,

  Check your PM.

  The only other reason I can think of for the variables dropping out of scope like that is if you are using GO to keep your transact sql in batches - this will make your variables go out os scope immediately after the GO statement.

  Have a good weekend.

  Steve

  We need men who can dream of things that never were.