INSERTING IN TEMP TABLE THRO DYNAMIC SQL

Question

Post reply

INSERTING IN TEMP TABLE THRO DYNAMIC SQL

Jtamil2001

SSC Enthusiast

Points: 140
More actions
March 3, 2004 at 7:07 am

#107750

I have a temp table in my stored procedure when I try to insert into the temp table thro dynamic sql, it says that table has to be defined. What could be the problem. i have added the code below
CREATE PROCEDURE USP_RULE
AS
declare @TABLE1 table
(
SlNo int identity(1,1), EqNum varchar(25),Pointnum varchar(25)
)
declare @EqNum varchar(25),@Pointnum varchar(25)
DECLARE @STRDBNAME VARCHAR(50)
SET @STRDBNAME = 'DB1'
EXEC('insert into '+@TABLE1+' select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

The procedure and the dynamic statement run in different scopes.

I think you can't use a table variable. *Better* would be a temp or permanent table like this

CREATE PROCEDURE USP_RULE

AS

CREATE table some_name

(

SlNo int identity(1,1), EqNum varchar(25),Pointnum varchar(25)

)

declare @EqNum varchar(25),@Pointnum varchar(25)

DECLARE @STRDBNAME VARCHAR(50)

SET @STRDBNAME = 'DB1'

EXEC('insert into some_name select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')

At least it compiles without problems.

BTW, you might want to have a look at this

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

srankin Say Hey Kid Points: 703 More actions · Answer 2

When you create a temporary table it is in scope for either the duration of the batch (Local Temporary Table) or for the duration of the connection (Global Temporary Table.)

A Local Temporary Table is identified by a single pound sign (#TempTable).

A Global Temporary Table is identified by a double pound sign (##TempTable).

Since dynamic SQL is, by definition, inside the scope of the batch it can see/access any temporary table available to the batch.

If a stored procedure is called from a batch and the stored procedure executed Dynamic SQL then both the stored procedure and the dynamic SQL have access to the temporary table.

What I see missing is what Frank alluded to with the Create Table Statement. The temporary table must exist within the scope of the Batch/Stored Procedure for the Dynamic SQL to see/access it.

This does not work in reverse for Local Temporary Tables though. If you create a Local Temporary Table (#) inside the Dynamic SQL, as soon as the Dynamic SQL completes the temporary table is out-of-scope and removed from tempdb.

I hope this helps.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

I believe this should help:

CREATE PROCEDURE USP_RULE

AS

CREATE TABLE #TABLE1

(

SlNo int identity(1,1), EqNum varchar(25),Pointnum varchar(25)

)

declare @EqNum varchar(25),@Pointnum varchar(25)

DECLARE @STRDBNAME VARCHAR(50)

SET @STRDBNAME = 'DB1'

EXEC('insert into #TABLE1 select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')

Prasad Bhogadi
www.inforaise.com

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

That's what I meant to show in my post above.

It should also work with temp tables.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jtamil2001 SSC Enthusiast Points: 140 More actions · Answer 5

Thanks Frank / Prasad

It worked out with temp table creation