Problem creating temporary table in SP

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

SQL Server Development (2000)

Problem creating temporary table in SP

Author

Topic

mb
Starting Member

16 Posts

Posted - 2002-05-01 : 14:05:00

Im trying to use a stored procedure to create a temporary table, however when I try to query the temporary table it says its an invalid object name.

Heres the code I use:

CREATE PROCEDURE PR_CreateCurrentUserTable
AS
CREATE TABLE #Current_user (UserName varchar(80))
INSERT into #Current_user (UserName) values (system_user)
GO

I then execute the Procedure:

exec PR_CreateCurrentUserTable

and try to query the newly created table

select * from #current_user

and I get this error message

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#current_user'.

Has anyone got any ideas what could be going wrong here.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-01 : 14:10:51

Once the procedure that creates the temp table completes, it's no longer available for use. From Books Online under "CREATE TABLE":

quote:
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

In order to SELECT from that temp table, you need to include the SELECT in the procedure that creates it.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-05-01 : 15:29:59

Maybe you can explain what you are trying to do, and we can help you figure out how to do it. it sounds liek you just need to combine your create / insert with that select statement.

something like this...
CREATE PROCEDURE PR_CreateCurrentUserTable
AS
--If you are using ADO and returning a recordset, you probably need
--this "NOCOUNT" or you will get an error when you
--try to access your recordset
SET NOCOUNT ON
CREATE TABLE #Current_user (UserName varchar(80))
INSERT INTO #Current_user (UserName) values (system_user)

SELECT * FROM #Current_user
SET NOCOUNT OFF
GO

If you are just trying to get the system_user this would work
CREATE PROCEDURE PR_CreateCurrentUserTable
AS

SELECT system_user
GO

Another option might be Global Temp tables. You need to be very careful with them because they are NOT local to the connection. It's very much like creating a regular table that everyone can see.

Search for global temporary tables in the BOL.

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources