473,216 Members | 1,484 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

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 4311
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can...
2
by: M Wells | last post by:
Hi All, I'm wondering if anyone can tell me if it's possible to search for stored procedures by their contents? I tend to leave very descriptive notes in stored procedures and they're...
7
by: Anthony Robinson | last post by:
Have been encountering an odd issue. Every now and again, certain packages of stored procedures just become invalid. I'm aware that dropping or altering an underlying table would render a package...
1
by: Thomasb | last post by:
Hi! Guess this is a typical beginners question :-) DB1 z/OS ver. 7.1 Two stored procedures each with a cursor. There is no syntax like
2
by: Twan Kennis | last post by:
Question: How do I pass a returning resultset from a nested Stored Procedure (which opens a cursor including option "WITH RETURN TO CALLER") as a returning resultset from it's own? When I...
1
by: sasachi sachi sachi | last post by:
Hi there, I have a data manipulation process written in a Nested Stored procedure that have four levels deeper. When I run these individual procedures individually they all seems to be fine....
5
by: Wael | last post by:
Hi, I have the following stored procedure that does some processing and puts the result in a temporary table. I tried several things that procedure to display output that I can access with...
0
by: balaji krishna | last post by:
Hi, I need to handle the return set from COBOL stored procedure from my invoking Java program. I do not know, how many rows the stored proc SQL fetches.I have declared the cursor in that proc, but i...
14
by: jehugaleahsa | last post by:
Hello: I am working with Oracle .NET Stored Procedures. I would like to know how to return the results of a SELECT statement. I have tried returning a OracleRefCursor and a DataTable, but...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.