Passing Temporary Tables in a Store Procedure

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)

Passing Temporary Tables in a Store Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-17 : 11:16:40

Harish writes "Hi,

Here is a small example of a procedure which simply return the rows from a table. I am using SQL Server 7.0

CREATE PROCEDURE [F_MYPROC] AS
SELECT * FROM T_Employee

Now the my problem is can I pass a name of temporary table, which
is dynamically created in an application (VB) and from there I call this procedure to return the row of this temp table.

Following example I have tried, but failed. System given me 'Incorrect Syntax Error' at select statement

CREATE PROCEDURE [F_MYPROC] @MYTABLE VARCHAR(30)
AS
SELECT * FROM @MYTABLE

!!! CAN ANYBODY HELP ME OUT !!!

Because from this example I can create some complex procedure which really need in my application."

lfmn
Posting Yak Master

141 Posts

Posted - 2002-01-17 : 13:06:05

We had the same problem with ASP pages where I work. Our 'Fix' was to use a random number generator combined with a string to create a table name, pass the permanent 'temp' table name into the process and then delete the table after it was no longer needed. We also have a job which runs every night to sweep up any of these permanent 'temp' tables which escaped being dropped.

cursors are like hammers - sometimes you have to use them, but watch your thumb!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources