Passing temporary tables as parameters in SQL Server

Question

Passing temporary tables as parameters in SQL Server

Sanjay Prabhakar Kamath

SSC-Addicted

Points: 441
More actions
March 24, 2005 at 2:49 am

#164891

hi,
how to i pass a temporary table as a parameter to a stored procedure? I know you cant use a table variable to do that.
Regds

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

Wouldn't it be better to generate the temporary table inside the stored procedure? That way you just pass the parameters needed to generate it along with any other parameters you need.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

http://www.sommarskog.se/share_data.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Sanjay Prabhakar Kamath SSC-Addicted Points: 441 More actions · Answer 3

hi,

all i'd like to know is whether it is possible to pass a temporary table as a parameter in a stored procedure???????????????????????????????????

If so, how.

Regds

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

TANSTAAFL.

Short answer, no. But please read the article I referenced

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 5

As Frank told you, it is impossible to pass a table as a parameter to a stored proc.

I personnally would use in your place a global temporary table (##Temp) which is available to all procs. The problem with the global temp table is that you have to manage it's lifecycle (from creation until destruction)

Bye
Gabor

MattieNH SSCertifiable Points: 7463 More actions · Answer 6

I personnally would use in your place a global temporary table (##Temp) which is available to all procs. The problem with the global temp table is that you have to manage it's lifecycle (from creation until destruction)

The other problem with global temp tables is that you can only have one instance of the named table at a time, so they don't work really well in multi-user applications.

Mattie

dongerard1 SSC Veteran Points: 206 More actions · Answer 7

You can use the table variable as a return from a UDF. I have used this successfully in the past.

Hope this helps!

Don