Re: nested stored procedures and returning lots of rows
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 21 May 2008 21:37:21 +0000 (UTC)
(codefragment@xxxxxxxxxxxxxx) writes:
I have a chunky bit of sql that I will want to call from a number of
places. It will return a
few thousand rows. Whats the best way of structuring this?
1) I initially thought of using nested stored procedures and returning
the result in a temporary
table. However the scope of the temporary table seems to be limited to
the stored procedure
its created in so unless I create the temporary table in every stored
proc that needs to use
this generic one it won't work. If I create it in the generic stored
proc then the called stored procs can't see it
2) Insert the rows into a real table with a guid. Once the call has
finished delete those rows.
Scoping is now not an issue?
3) A view? I haven't had any experience with these but I don't believe
they can take arguments
which makes them useless for this
I discuss possible alternatives on an article on my web site,
http://www.sommarskog.se/share_data.html. I cover both your first and
second alternative there. Views are not doable, but a function could
make it for you.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- nested stored procedures and returning lots of rows
- From: codefragment
- nested stored procedures and returning lots of rows
- Prev by Date: Re: Checking for duplicates in a database table
- Next by Date: Re: select from row1 to row n
- Previous by thread: Re: nested stored procedures and returning lots of rows
- Next by thread: Checking for duplicates in a database table
- Index(es):
Relevant Pages
|
