Using a pointer to a local varaiable in Dynamic SQ

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using a pointer to a local varaiable in Dynamic SQ

Author

Topic

RakuRay
Starting Member

2 Posts

Posted - 2009-11-06 : 14:17:42

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?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-06 : 14:30:04

DECLARE @SD1 real
SET @SD1 = 1.1

DECLARE @SQLCmd varchar(1000)
select @SQLCmd = 'SELECT MyNumber = ' + CONVERT(varchar(10),@SD1)

Jim

N.B. Always declare varchar with how long you may need it to be

Everyday I learn something that somebody else already knew

RakuRay
Starting Member

2 Posts

Posted - 2009-11-06 : 14:34:43

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-06 : 14:58:45

SET @SDNAME = CONVERT(varchar(10),@SD1))

SET @SQLCmd = 'SELECT MyNumber = ' + @SDName

Post your code if you want an answer that isn't a guess about what's on your mind. It's Friday and my psychic powers are greatly diminished.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources