Using a pointer to a local varaiable in Dynamic SQL

[RakuRay]

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?

[ck9663]

Why do you need another variable? Can't you just do a

DECLARE @SQLCmd varchar
SET @SQLCmd = 'SELECT MyNumber = ' + cast(@SD1 as varchar(10))

Happy Coding!!!

--- CK

[RakuRay]

Not that simple

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

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

set nocount on
declare @x1 int, @x2 int, @x3 int, @varname char(3)
declare @sqlstatement  varchar(max)

select @x1 = 1, @x2 = 2, @x3 = 3
set @varname = '@x3'

select @varname 
set @sqlstatement = 
 ('

declare @x1 int, @x2 int, @x3 int, @varname char(3)

set @x1 = ' + cast(@x1 as varchar(5)) + ' 
set @x2 = ' + cast(@x2 as varchar(5)) + ' 
set @x3 = ' + cast(@x3 as varchar(5)) + ' 

set @varname = ' + @varname + 
'
select @varname

')

exec (@sqlstatement)

Try playing around the value of:

set @varname = '@x3'

Happy Coding!!!


--- CK

[nbiswas]

Instead of SET @SDName = '@SD1' use

SET @SDName = @SD1

. No quotes