Retrieve variable value from dynamically concatenated variable

Question

Retrieve variable value from dynamically concatenated variable

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Jason Hanson (11/7/2008)
Thge thing is....You do not know which variables can be created, because they can be different per project.

Then they should not be in variables in the first place, they should be name/value pairs in a temporary table. And really, everything that you allude to here, and every possible solution that we could give you actually relates to the larger context of the whole procedure and script that you do not want to show us.

For instance:
Proj 1 - Address, City, State
Proj 2 - Phone Number, Zip
Proj 3 - First_Name, Last_Name

The more I hear about this, the worse it sounds. What you are describing is tabular data. If you want to manipulate and use it in a flexible way, then it needs to be in a tabular (table set) format, not in scalar variables. (And you need to use set operations, not cursors.)

So I'm trying to avoid using the case statement to check for the names. How do I go about doing this without checking for the names (CASE), as the names could different per populated project.

You don't. Not with your overall approach. There is no way to do this through variables with varying dynamic SQL contexts. Local variables are entirely local in scope and visibility: whereas dynamic SQL makes a new batch scope for every invocation that will not be able to see the original scope. Temporary tables are the SOP way to pass context between dynamic SQL scopes.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

rbarryyoung (11/7/2008)
Local variables are entirely local in scope and visibility: whereas dynamic SQL makes a new batch scope for every invocation that will not be able to see the original scope.

Not 100% true... sp_ExecuteSql.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Jeff Moden (11/7/2008)
rbarryyoung (11/7/2008)
Local variables are entirely local in scope and visibility: whereas dynamic SQL makes a new batch scope for every invocation that will not be able to see the original scope.
Not 100% true... sp_ExecuteSql.

Yep, but they're parameters, not variables. You could pass the variables through the parameters, but then the same problem remains: you need to use different variable names depending on the project... and you're right back where you started from.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]