Dynamically refer to a variable

[KornGeek]

I have a situation similar to the following:

--Set up some variables with values
DECLARE @V1 varchar(10)
SET @V1 = 'A'
DECLARE @V2 varchar(10)
SET @V2 = 'B'
DECLARE @V2 varchar(10)
SET @V3 = 'C'

DECLARE @VName varchar(10)
SET @VName = '@V1'
--WHERE @VName is set to the name of one of the variables


I now want to get the value of the variable referred to by @VName.

This will save me a ton of work if I can figure out how to make this happen.

Thanks for any insight you might be able to provide.

 

[markros]

There is no way to do this in T-SQL. It doesn't have macro or evaluate or name resolution. For column names you can use dynamic SQL, but there is nothing (well, expect dynamic SQL again) for the variables.

PluralSight Learning Library

 

[simian336]

You could put your variables in a small table.

Preferences
Var Value
V1, 'A'
V2, 'B'
V3, 'C'

DECLARE @VName varchar(10)

SET @VName = Select Value from preferences where Var=V1

'V1'--WHERE @VName is set to the name of one of the variables

Simi

 

[Qik3Coder]

Why don't you use a "local variable" like you would in "regular" code?

Declare @varA varchar, @varB varchar, @varC varchar, @loopVar
Select @varA = 'A', @varB = 'B', @varC = 'C'

if (SomeCriteria)
 BEGIN
  Select @loopVar = @varA
 END

Select * from someTable where col = @loopVar

Unless you're trying to do something more complex, like dynamically generate SQL statements.
In this case you will wind up with dynamic sql generating dynamic sql. This is not impossible, but is a *&()* to debug.

I would suggest getting the "inner" SQL working correctly.
1.) Translate this to a dynamic SQL statement (with no dynamic parts. This will require a string long enough to hold the SQL statement, and this sometimes takes more than 1 variable.
2.)Once you have it executing the non-dynamic dynamic sql, then take and create the variable bits and get that working (Still with fixed values).
3.) Now add the code to spin through your dynamic bits executing the dynamic sql.

Lodlaiden

You've got questions and source code. We want both!

 

[KornGeek]

Thank you for the responses. It looks like I'm going to have to scrap this for now. There are possibilities including incorporating a table or some C# code, but it goes beyond the scope of what I can do right now.

I have a series of databases that are identical in structure (with identical stored procedures and functions), but with different data sets (there are very complicated reasons we chose to implement such an unorthodox plan). I'm modifying a bunch of stored procedures to check to ensure that they are in the correct database, and, if not, to generate dynamic sql to execute the corresponding stored procedure in the correct database.

Updating the stored procedures is mostly a bunch of copy and paste, but we still have to manually edit the parameters into the dynamic sql. I found a way to programatically determine the parameter names and data types and was hoping to completely automate the dynamic sql generation.

At this point, it looks like further researching this will take more time than simply hand-modifying the stored procedures. (Even though it would be really cool to completely automate it.)

 

[Qik3Coder]

You may want to spend that time creating an actual deployment process for your database.
That way when you deploy updates each database is identical.


The intensity of your deployment methodology is normally directly corrected to the number of times you have to deploy.

1-few internal (eternal) databases - single specific patch script.
1-few internal (temp) databases - version based patch script(s). Candidate for lightweight scripting if frequency is high.

>few internal (eternal) databases - single specific patch script.
>few internal (temp) databases - lightweight scripting app (C#, batch, Powershell)

external databases (shrinkwrap software)
normally this is lightweight to heavyweight depending upon the intensity/likelyhood of database changes/need to preserve systemic data.


Lod


You've got questions and source code. We want both!