October 30, 2008 at 7:46 am
Hi Forum,
I need to create a Stored Procedure which will receive the name of a table as parameter and perform numerous operations (probably using a 'Curse her' :crazy:) on that table. I know this sound unorthodox but there's a few reasons why I have to do things this way.
In the procedure I don't want to have to build multiple SQL command strings and call Exec (@SQL) repeatedly so I'm trying to copy the source table to a #temporary table so I can use regular T-SQL syntax throughout the procedure.
Here's a short script to illustrate what I'm trying to do.
/* First let's create a table to work with. In 'real life' this would be created by a 3rd party app which will call the procedure */
Create Table myTable (Col1 char(20))
Go
/* Add some data to it */
insert into myTable
Select 'A' union all
Select 'B' union all
Select 'C'
Go
/* Create the Procedure */
Create Procedure DoMyStuff
(
@TableName char(100)
)
as
Declare @CopyCommand char(100)
Set @CopyCommand = 'select * into #T from ' + rtrim(@TableName)
Exec (@CopyCommand)
Select * from #T
Go
/* Test it ! */
Exec DoMyStuff 'MyTable'
For some reason the #T table doesn't get created, or if it does then I don't know where because when I run the procedure I get the result shown below and I can't find a #T table in tempdb.
Msg 208, Level 16, State 0, Procedure DoMyStuff, Line 8
Invalid object name '#T'.
What I am doing wrong ?
October 30, 2008 at 8:08 am
Temporary tables are created within context and when you use dynamic sql you have created a new context, so when you
create a temporary table within dynamic sql it is only available within that context.
To return your data you would need to do the select from #temp within the dynamic sql.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 30, 2008 at 8:08 am
eric (10/30/2008)
Hi Forum,I need to create a Stored Procedure which will receive the name of a table as parameter and perform numerous operations (probably using a 'Curse her' :crazy:) on that table. I know this sound unorthodox but there's a few reasons why I have to do things this way.
In the procedure I don't want to have to build multiple SQL command strings and call Exec (@SQL) repeatedly so I'm trying to copy the source table to a #temporary table so I can use regular T-SQL syntax throughout the procedure.
Here's a short script to illustrate what I'm trying to do.
/* First let's create a table to work with. In 'real life' this would be created by a 3rd party app which will call the procedure */
Create Table myTable (Col1 char(20))
Go
/* Add some data to it */
insert into myTable
Select 'A' union all
Select 'B' union all
Select 'C'
Go
/* Create the Procedure */
Create Procedure DoMyStuff
(
@TableName char(100)
)
as
Declare @CopyCommand char(100)
Set @CopyCommand = 'select * into #T from ' + rtrim(@TableName)
Exec (@CopyCommand)
Select * from #T
Go
/* Test it ! */
Exec DoMyStuff 'MyTable'
For some reason the #T table doesn't get created, or if it does then I don't know where because when I run the procedure I get the result shown below and I can't find a #T table in tempdb.
Msg 208, Level 16, State 0, Procedure DoMyStuff, Line 8
Invalid object name '#T'.
What I am doing wrong ?
You are creating a local temporary table. It is being created when you do the Exec (@CopyCommand), however it is deleted by SQL Server when the command completes as it is created in a different session. You either need to create a permanent table in the dynamic sql, or use a global temporary table, and then explicitly drop the table when you are done.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply