| Author |
Topic |
|
ulookn2
Starting Member
7 Posts |
 Posted - 2008-11-05 : 17:16:04
|
| Hello,I'm using dynamic sql to create a few cursors cursors. Here is what I am trying to do: I want to concatenate a variable from the cursor to a previously declared variable having the concatenated 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 previous steps) 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' |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-05 : 18:07:25
|
Three bad things.1) Dynamic SQL2) Cursor in dynamic SQL3) Getting variable value by pointer
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
ulookn2
Starting Member
7 Posts |
Posted - 2008-11-05 : 19:50:49
|
Yeah, but it has to be done for this project. Any suggestions on how to solve the problem?quote:
Originally posted by Peso
Three bad things.1) Dynamic SQL2) Cursor in dynamic SQL3) Getting variable value by pointer
E 12°55'05.63"N 56°04'39.26"
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-05 : 22:49:35
|
| show some sample data to illustrate your problem. |
|
|
|
ulookn2
Starting Member
7 Posts |
Posted - 2008-11-05 : 23:45:51
|
| The values contained within the values should display address (Examples) 123 Park Street 131 Hatt Rd508 Suhey DriveInstead, it displays the variable names that were previously declared. (Examples) @ADDRESSset @qry =' declare @target_variable2 varchar(50), @DRname varchar(75), @absComp varchar(75) ' + char(10) +'declare retrieve_variable_list2 CURSOR for ' + char(10) +'select target_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' + 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 + CHAR(10) +'end' + char(10) + 'close retrieve_variable_list2' + char(10) +'deallocate retrieve_variable_list2' |
|
|
|
ulookn2
Starting Member
7 Posts |
Posted - 2008-11-05 : 23:57:41
|
| Let me clear it up a little more. 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 use a cursor to retrieve these variables, I need to display the value within. When I dynamically create the variable name from the cursor and try to print it, it prints the variable name (Ex: @dr_Address). But when I hardcode the actual variable name, it prints the variable value (123 Park Street). Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-06 : 00:17:29
|
| why are you using cursor? whats your exact requirement? |
|
|
|
ulookn2
Starting Member
7 Posts |
Posted - 2008-11-06 : 00:26:22
|
Well I have to sync the data fields within two systems. The fields of both systems are populated within lookup tables. If can just retrieve the values within the variables, I'll be all set. quote:
Originally posted by visakh16
why are you using cursor? whats your exact requirement?
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-06 : 02:54:18
|
| but for sync two tables is it enough to go for set based approach. just compare based on primary key value and set the values of other fields if not same. why going for row by row comparison? |
|
|
|
ulookn2
Starting Member
7 Posts |
Posted - 2008-11-06 : 08:44:14
|
| Synchronization will occur per project within the table. Each project will have have different fields associated with it. This project has Address, City, State. The next may have Phone Number, Address, Name. How can I get the value from this variable? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-06 : 09:15:39
|
| So each project will have data in different table? I'm still not fully understanding your scenario. |
|
|
|
ulookn2
Starting Member
7 Posts |
Posted - 2008-11-06 : 13:17:05
|
| The same table, but different values within the fields. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-11-06 : 16:28:00
|
| You might try sp_executesql. I'm not sure if it can execute cursors or not. If you want to take the time to explain the system in more detail we can probably show you how to avoid cursors and dynamic sql. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-07 : 00:14:43
|
quote:
Originally posted by ulookn2
The same table, but different values within the fields.
if same table then whats the need of dynamic sql? you can achieve this using a normal set based solution. Can you post some example data to illustrate the scenario? |
|
|
|
|
Retrieve variable value from Dynamic SQL declarat