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

Expand|Select|Wrap|Line Numbers

 
DECLARE @SQLCmd varchar

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

Happy Coding!!!

--- CK

RakuRay

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:

Expand|Select|Wrap|Line Numbers

  
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:

Expand|Select|Wrap|Line Numbers

set @varname = '@x3'

Happy Coding!!!

--- CK

nbiswas

Instead of SET @SDName = '@SD1' use

Quote:

SET @SDName = @SD1

. No quotes

Using a pointer to a local varaiable in Dynamic SQL

re: Using a pointer to a local varaiable in Dynamic SQL

re: Using a pointer to a local varaiable in Dynamic SQL

re: Using a pointer to a local varaiable in Dynamic SQL

re: Using a pointer to a local varaiable in Dynamic SQL

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about