Using a pointer to a local varaiable 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

A dynamic sql code is executed in another context and will not have access to local variables defined in the main script. Said that, you could declare the variables inside the dynamic sql or you could use parameters or tables that could be accessed by the dynamic code.

declare @stmt nvarchar(4000);

set @stmt = N'declare @i int; set @i = 11 select MyNumber = @i;'

exec sp_Executesql @stmt;
go

declare @stmt nvarchar(4000);
declare @params nvarchar(4000);

set @stmt = N'select MyNumber = @i;'
set @params = N'@i int';

exec sp_Executesql @stmt, @params, 11;
go


How to Share Data Between Stored Procedures
http://www.sommarskog.se/share_data.html


AMB

Answer 2

Another solution is to use #temptable. The child process "sees" the parent's #temptable.
Just populate the (one row, several columns) #temptable with the current local variable data.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com