Creating a temp table inside a stored proc

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

General SQL Server Forums

New to SQL Server Programming

Creating a temp table inside a stored proc

Author

Topic

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2009-03-18 : 11:23:49

Hi all,

I am trying to create a Temp table inisde a stored proc taht uses a dynamic sql statement. After executing the Stored proc.. everything seems to be working fine , but when I add a select statment to the stored proc to retive the data from the temp table.. It says invalid object temptable

Here is what I did :

The select statment i am providing here is just a sample.. actually my sql statmnet checks for a lot of if conditions for all the parameters passed and appends to the sql statment

Stored Proc written :

ALTER procedure [dbo].[test]
as
begin

declare @sql as varchar(1000)
set @sql = 'select * into #TempTable
from Employees '

print @sql
exec @sql

select * from #TempTable
Drop table #TempTable

End

Result:

After executing this stored proc , This is what I am getting in the messages:

select * into #TempTable
from Employees

Msg 2812, Level 16, State 62, Line 13
Could not find stored procedure 'select * into #TempTable
from Employees '.

Msg 208, Level 16, State 0, Procedure test, Line 15
Invalid object name '#TempTable'.

(1 row(s) affected)

Can some body please help me where I am going wrong??/
Your help will be very much appreciated

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-18 : 11:37:20

Can't be done in this way, what you can do is create the temp table first (and give it a structure) and then exec your sql into it:

create table #tmptble (a int, b varchar(10))
insert into #tmptable exec @sql

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-18 : 11:43:10

Absolutely!

A temp table created inside a block of dynamic SQL is local to that score and will no longer exist once the dynamic execution is finished.

As Rick says -- you can create a temp table before executing the dynamic sql -- it will be available to dynamic sql.

You don't actually need to do the


INSERT <balh>
EXEC <blah>

syntax unless you want to -- I think that is actualy a row based operation so is likely to be slow. The table will be available for you to reference as you would normally from insode the dynamic block -- you can do something like

IF OBJECT_ID('tempdb..#foo') IS NOT NULL DROP TABLE #foo

CREATE TABLE #foo (
    [val] VARCHAR(50)
    )

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'

INSERT INTO #foo (
	[val]
	)
      SELECT ''aaa''
UNION SELECT ''bbb''
UNION SELECT ''ccc'''

EXEC (@sql)
SELECT * FROM #foo

And it will work fine.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2009-03-18 : 11:47:47

Thanks for ur quick replies. Let me try that way and update you guys.

Once again thanks for ur suggestions

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2009-03-18 : 13:05:40

I tried modifying he proc as follows:

ALTER procedure [dbo].[test]
as
begin

create table #temp( Employeeid int)

declare @sql as varchar(5000)
set @sql = 'Insert into #temp( Employeeid) (select Employeeid
from Employee )'

print @sql
--insert into #temp exec @sql
exec @sql

select * from #Temp

Drop table #Temp

End

but still not working. Getting this result:

Insert into #temp( Employeeid) (select Employeeid
from Employee )

Msg 2812, Level 16, State 62, Line 17
Could not find stored procedure 'Insert into #temp( Employeeid) (select Employeeid from Employee)'.

(0 row(s) affected)

(1 row(s) affected)

Please help. Very Urgent..

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-18 : 13:40:21

try this,


ALTER procedure [dbo].[test]
as
begin


create table #temp( Employeeid int)

declare @sql as varchar(5000)
set @sql = 'Insert into #temp(Employeeid) select Employeeid 
from Employee '

--print @sql
--insert into #temp exec @sql
exec (@sql)

select * from #Temp

Drop table #Temp

End

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-19 : 06:06:58

Yeah -- you need the brackets round the @sql in EXEC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources