Retrieve variable value from dynamically concatenated variable

Question

Retrieve variable value from dynamically concatenated variable

Jason Hanson

Newbie

Points: 9
More actions
November 5, 2008 at 11:53 am

#72245

Hello,
I'm using dynamic sql to create cursors. Here is what I am trying to do: I want to concatenate a variable from the cursor to a previously declared variable with the same name Ex: The variable name is @dr_address. And the field address is passed into the cursor from the @target_variable2 variable. The problem is, I want the actual value within the @dr_address variable (from prior code) to be printed, but it prints '@dr_address' instead. Please help.
set @qry =
' declare @target_variable2 varchar(50), @absvariable2 varchar(50), @DRname varchar(75), @absComp varchar(75) ' + char(10) +
'declare retrieve_variable_list2 CURSOR for ' + char(10) +
'select target_variable, ABS_VARIABLE
from Fieldmap
where project_id = ' + '''' +@Project_ID + '''' +
' order by target_variable ' + char(10) +
'open retrieve_variable_list2' + char(10) +
'fetch next from retrieve_variable_list2 into @target_variable2, @absvariable2' + char(10) +
'while (@@fetch_status = 0)' + char(10) +
'begin' + char(10) +
'set @DRname = @target_variable2' + char(10) +
'set @DRname = ''@dr_'' + @target_variable2' + char(10) +
'PRINT @DRname '
'fetch next from retrieve_variable_list2 into @target_variable2, @absvariable2' + CHAR(10) +
'end' + char(10) +
'close retrieve_variable_list2' + char(10) +
'deallocate retrieve_variable_list2' + CHAR(10) +

Viewing 15 posts - 1 through 15 (of 18 total)

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

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

I don't see anything in the code that requires dynamic SQL... why the requirement to use it? I won't even ask why you think you need a cursor... 😛

--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)

Jason Hanson Newbie Points: 9 More actions · Answer 2

Well, this is only part of the code. Everything else works. I just need to figure out how to retrieve the value within the concatenated created variable. and not the variable_name. Any ideas?

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

Well, sure...

http://www.sqlservercentral.com/articles/Test+Data/61572/

--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)

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 4

More explanation about problem is found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113928

N 56°04'39.16"
E 12°55'05.25"

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

Peso (11/6/2008)
More explanation about problem is found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113928

How on Earth do you keep track of these things, Peter?

--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)

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 6

SwePeso

SSC-Dedicated

Points: 39758

November 6, 2008 at 6:56 am

#894827

Pure luck I guess.

N 56°04'39.16"
E 12°55'05.25"

Jason Hanson Newbie Points: 9 More actions · Answer 7

This solution had nothing to do with my problem. I just need to retrieve the value within a dynamically created variable instead of the program thinking I want to retrieve just the variable name.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 8

toward the end of your code you have this:

'set @DRname = ''@dr_'' + @target_variable2' + char(10) +

'PRINT @DRname '

which is setting @DRname to the string '@dr_address', you haven't assigned the value into @dr_address yet, unless we're missing something.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Jason Hanson Newbie Points: 9 More actions · Answer 9

The queried table contains field names (Address, City, State). Previously in some dynamic SQL code, a concatenated string declaring all of these as variables was used (Ex: @dr_Address varchar(50), @dr_City varchar(50), @dr_State varchar(2)). So now when I try to retrieve these variables, I need to display the value within. Keep in mind, the variable name was dynamically created. So how would I print it or pass it when its dynamically created? But when I hardcode the actual variable name, it prints the variable value (123 Park Street).

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 10

I think you're trying to create too many levels between what you're attempting and dynamic generation of queries and variables.

You also have double quotes around ''@dr_'' just before adding the temp variable in, if you remove those it might work better, but will probably just require you to redeclare @dr_address from within your dynamically generated query (did me, anyway)

see below:

DECLARE @sql varchar(255),@drname varchar(255),@dr_address varchar(255),@temp varchar(255)

SET @temp = 'address'

SELECT @sql = 'DECLARE @dr_'+@temp+' varchar(255),@drname varchar(255) SELECT @drname = ''@dr_''' + @temp

PRINT @sql

EXEC(@sql)

I was reading the post on sqlteam via Peso's link, and I think the suggestion they gave of explaining further might help most, as someone either here or on that site will likely be able to shortcut this process once we understand what you need better.

sorry,

Jon

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

This may well be the "Worst use of SQL, in 20 lines or less" that I have seen in quite some time... Nonetheless, here is how this can be done (based on the inadequate information you have given us, anyway):

set @qry =

' declare @target_variable2 varchar(50), @absvariable2 varchar(50), @DRname varchar(75), @absComp varchar(75)

declare retrieve_variable_list2 CURSOR for

select target_variable, ABS_VARIABLE

from Fieldmap

where project_id = ''' +@Project_ID + '''

order by target_variable

open retrieve_variable_list2

fetch next from retrieve_variable_list2 into @target_variable2, @absvariable2

while (@@fetch_status = 0)

begin

Set @DRname = @target_variable2

set @DRname = CASE @target_variable2

When ''Address'' Then @dr_Address

When ''City'' Then @dr_City

When ''State'' Then @dr_State

End

PRINT @DRname

fetch next from retrieve_variable_list2 into @target_variable2, @absvariable2

end

close retrieve_variable_list2

deallocate retrieve_variable_list2 '

Sorry, I couldn't help myself and cleaned-up some of it while I was fixing it.

[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]

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 12

rbarryyoung (11/7/2008)
This may well be the "Worst use of SQL, in 20 lines or less" that I have seen in quite some time...

We should post that as a contest . . .

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Jason Hanson Newbie Points: 9 More actions · Answer 13

Thge thing is....You do not know which variables can be created, because they can be different per project. For instance:

Proj 1 - Address, City, State

Proj 2 - Phone Number, Zip

Proj 3 - First_Name, Last_Name

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. So the variable declaration for these project would be as follows:

Proj 1 - @dr_Address, City, @dr_State

Proj 2 - @dr_Phone_Number, @dr_Zip

Proj 3 - @dr_First_Name, @dr_Last_Name

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 14

do you really need to name the variables differently depending on the parameters? can't you just call it @param1, @param2, @param3, and let the data figure out what's in what?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."