468,783 Members | 1,616 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,783 developers. It's quick & easy.

nested stored procedures and returning lots of rows

Hi
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

At the moment it seems the most efficient way is possibly to accept
the cut and paste of (1)
and go that way

Any suggestions?

ta
Jun 27 '08 #1
3 4142
http://www.sommarskog.se/share_data.html

Inline Functions, this looks like it might be what I'm after. The
shared stored proc will involve a few selects, joins and subqueries
but will return a single table. I am worried that this might cause the
optimiser a few headaches though
as I've had this problem in the past and the full combined stored proc
(the caller combined with this function) will
have lots of joins and left joins
Jun 27 '08 #2
but no nesting, didn't mention it but I need this too :-(

k, creating real tables and as this has to work in mssql and oracle
using guids
Jun 27 '08 #3
(co**********@googlemail.com) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Anthony Robinson | last post: by
1 post views Thread by Thomasb | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
2 posts views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.