By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Using a pointer to a local varaiable in Dynamic SQL

P: 2
I am having trouble with dynamic SQL.
Early in my code I assign data to a bunch of local variables.
I want to access these later in my code and use the data values.
The code example below shows a simplified example to explain what I am trying to do.

-- ----------------------------------------------
-- Declare and set the data into a local variable
-- ----------------------------------------------
DECLARE @SD1 real
SET @SD1 = 1.1

-- ----------------------------------------------------------
-- Declare and set a variable to point to data local variable
-- ----------------------------------------------------------
DECLARE @SDName varchar
SET @SDName = '@SD1'

-- ---------------------------------------
-- Declare and set the dynamic SQL command
-- ----------------------------------------
DECLARE @SQLCmd varchar
SET @SQLCmd = 'SELECT MyNumber = ' + @SDName

By running this code the @SQLCmd contains the following ...
SELECT MyNumber = @SD1

BUT what I REALLY want is for @SQLCmd to contain this ...
SELECT MyNumber = 1.1

How can I accomplish this?
Nov 6 '09 #1

✓ answered by ck9663

Could you post some portion of the loop? There might be other way of doing it.

But if you insist on doing it, try this technique:

Expand|Select|Wrap|Line Numbers
  1.  
  2. set nocount on
  3. declare @x1 int, @x2 int, @x3 int, @varname char(3)
  4. declare @sqlstatement  varchar(max)
  5.  
  6. select @x1 = 1, @x2 = 2, @x3 = 3
  7. set @varname = '@x3'
  8.  
  9. select @varname 
  10. set @sqlstatement = 
  11.  ('
  12.  
  13. declare @x1 int, @x2 int, @x3 int, @varname char(3)
  14.  
  15. set @x1 = ' + cast(@x1 as varchar(5)) + ' 
  16. set @x2 = ' + cast(@x2 as varchar(5)) + ' 
  17. set @x3 = ' + cast(@x3 as varchar(5)) + ' 
  18.  
  19. set @varname = ' + @varname + 
  20. '
  21. select @varname
  22.  
  23. ')
  24.  
  25. exec (@sqlstatement)
  26.  
  27.  
Try playing around the value of:

Expand|Select|Wrap|Line Numbers
  1. set @varname = '@x3'
  2.  
Happy Coding!!!


--- CK

Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
Why do you need another variable? Can't you just do a

Expand|Select|Wrap|Line Numbers
  1. DECLARE @SQLCmd varchar
  2. SET @SQLCmd = 'SELECT MyNumber = ' + cast(@SD1 as varchar(10))
  3.  
Happy Coding!!!

--- CK
Nov 6 '09 #2

P: 2
I appreciate your answer and quick response but sorry to say that your code is using the @SD1 local variable directly. What I want to do is use the @SDName variable that is equated to the name @SD1. I need to do this because the actual @SD1 variable name will change within a loop so that @SDName could be equal to @SD1 or @SD2 or ... @SD500 at some point.

Thanks
Nov 6 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Could you post some portion of the loop? There might be other way of doing it.

But if you insist on doing it, try this technique:

Expand|Select|Wrap|Line Numbers
  1.  
  2. set nocount on
  3. declare @x1 int, @x2 int, @x3 int, @varname char(3)
  4. declare @sqlstatement  varchar(max)
  5.  
  6. select @x1 = 1, @x2 = 2, @x3 = 3
  7. set @varname = '@x3'
  8.  
  9. select @varname 
  10. set @sqlstatement = 
  11.  ('
  12.  
  13. declare @x1 int, @x2 int, @x3 int, @varname char(3)
  14.  
  15. set @x1 = ' + cast(@x1 as varchar(5)) + ' 
  16. set @x2 = ' + cast(@x2 as varchar(5)) + ' 
  17. set @x3 = ' + cast(@x3 as varchar(5)) + ' 
  18.  
  19. set @varname = ' + @varname + 
  20. '
  21. select @varname
  22.  
  23. ')
  24.  
  25. exec (@sqlstatement)
  26.  
  27.  
Try playing around the value of:

Expand|Select|Wrap|Line Numbers
  1. set @varname = '@x3'
  2.  
Happy Coding!!!


--- CK
Nov 6 '09 #4

nbiswas
100+
P: 149
Instead of SET @SDName = '@SD1' use
SET @SDName = @SD1
. No quotes
Nov 9 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.