Problemin creating a temp table

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

SQL Server 2000 Forums

Transact-SQL (2000)

Problemin creating a temp table

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-03 : 13:17:30

Hi
I have some problem in tratin a temp table form this stored proceudre

CREATE PROCEDURE abc
AS
DECLARE @tab varchar(100)
Set @tab='CREATE table #TEMPABC(TBNAME sysname,ColCount int)'
EXEC(@tab)
print (@tab)

when I run Select * from #TEMPABC ,its gives me error:Invalid object name '#TEMPABC'.
Can someone help me.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-03 : 13:20:08

That's because you are using dynamic sql. Dynamic sql does not run in the same context of the stored procedure. So the rest of the code in your stored procedure can't see the temporary table because they are running in different contexts.

And this to your list of reasons why not to use dynamic sql.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-03 : 13:26:24

Now If iam using this command, i have same error

CREATE PROCEDURE abc @filename varchar(12)
AS
CREATE table #TEMPABC(TBNAME sysname,ColCount int)

Thanks for ur reply

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-03 : 13:27:46

I'll need to see more of your code because the code that you are providing does not error out. Please provide a better example.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-03 : 13:45:22

Hi Tara,
i am running only the above written code and when i run
Select * from #TEMPABC
but when i am running
CREATE table #TEMPABC(TBNAME sysname,ColCount int) it is working good.

So my problem is stored proc and i have to use it in procedure

thanx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-03 : 13:50:44

It works fine in a stored procedure too. I need to see your code to see what you are doing wrong. Please show us exactly what you are doing. As an example of what works fine:

CREATE PROC SomeProc
AS

SET NOCOUNT ON

CREATE TABLE #Temp
(Column1 INT NOT NULL)

INSERT INTO #Temp VALUES(1)
INSERT INTO #Temp VALUES(2)

SELECT * FROM #Temp

RETURN

-------end of stored proc--------
Now to prove it works, run:

EXEC SomeProc

You should see 1 and 2 as the result set.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-03 : 14:04:19

I think i have some server problem caz i just ran ur code, and the error is same that invalid object #temp, though the proc complies fine and execute too, but when i run select * from #temp then i got error msg.
Do u have any idea what is the problem. do i have to do some changes in server settings.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-03 : 14:07:21

You can not run SELECT * FROM #Temp outside of the stored procedure. The SELECT is done inside the stored procedure like in my example.

Run this code (all of it at once, do not run it a little at a time) and tell me if you get 1 and 2 returned:



CREATE PROC SomeProc
AS

SET NOCOUNT ON

CREATE TABLE #Temp
(Column1 INT NOT NULL)

INSERT INTO #Temp VALUES(1)
INSERT INTO #Temp VALUES(2)

SELECT * FROM #Temp

RETURN

EXEC SomeProc

DROP PROC SomeProc
GO