Using a pointer to a local variable in Dynamic SQL

Question

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?

Answer 1

Have you tried not quoting @SD1 in the @SDName declaration?

Answer 2

DECLARE @SQLCmd varchar
SET @SQLCmd  = 'SELECT MyNumber = ' + CAST(@SD1 AS VARCHAR)

VARCHAR defaults to 30 characters I believe which should be large enough. If you want to make this a parameterized query you could execute it using sp_executesql and pass the parameter value in.

Answer 3

.

DECLARE @SD1  real
    SET @SD1 = 1.1

DECLARE @SQLCmd nvarchar
    SET @SQLCmd = 'SELECT MyNumber = CAST(@SDName AS varchar)'

EXEC sp_executesql @SQLCmd, N'@SDName real', @SD1