August 10, 2005 at 3:41 am
Hi Folks
Hope someone can help me with this. I'm a bit of a newbie with SQL Server - but have a fair bit of Oracle experience.
I'm busy converting the database component of an application from Oracle into SQL Server 2000. We've got several cases where one stored procedure calls another - and the second SP, which is called by the first one, returns a recordset which the first SP then uses to update a table etc.
We set things up this way to avoid code duplication, for reasons of maintenance, and have many instances within our code of one SP being called by several other SPs.
How do I make the first SP capture the recordset returned from the second SP so it can insert that recordset into a table?
In Oracle, we did this by using a cursor as an output parameter, then having the calling SP step through the rows of that cursor; but I see no way to do this in SQL Server.
If anyone has any suggestions, I'd be most grateful to hear them.
David.
August 10, 2005 at 4:35 am
I think that table variables might give you what you need - they can be passed as parameters to/from stored procedures - try reading the on-line help and see how you get on.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 10, 2005 at 5:00 am
Hi Phil
Thanx for that. I'd already thought about table variables - but, SQL Server 2000 won't allow them to be used as output parameters from an SP, only as a return value from a function.
There is one other grey area here too.
We use ADO for the interface between the database component and the front-end VB component of our application; and ADO has, in the past, had problems with Oracle PL/SQL tables being returned as output parameters. I suspect that this might also be the case with table variables.
This wouldn't be an issue if the nested SP was only called by other SPs - but that isn't always the case. So whatever form we return its recordset data in has to be readable both via ADO and T-SQL.
If you or anyone else can throw any light on the ADO question, or suggest any way to get the calling SP to pick up the recordset of the SP it is calling, I'd be most grateful to hear it.
Thanx
Dave.
August 10, 2005 at 6:25 am
This would seem like a lousy workaround,
from the "main" sp :
insert into #temptable (col1, col2)
exec dbo.SubSP params
Now you have a copy of the recordset in a temp table. Then you can manipulate it and the calling application would still receive the data. The downside of this is that if you want to update the data once in the main sp, you have to rejoin to the actual table which is just not as efficient as doing it right away in the sub sp.
August 10, 2005 at 6:47 am
Seems to me a bit like a case of wanting to eat the cake and still have it.
On one hand, the proc's in question should return a resultset to the calling client (eg ADO), on the other hand sometimes it should not return anything, but instead the results should be further worked upon by another procedure...
Whenever you have something like SELECT <column | constant> in a procedure, that result will always be sent straight to the client.
So, if you have an inner proc like:
create proc innerProc
as
select count(*) from sysobjects
return
go
and you call it from another proc: ( exec outerProc )
create proc outerProc
as
exec innerProc
return
go
..the only thing that will happen is that the result from the select will be sent to the client. outerProc can't do anything with the results.
The only way to work with intermediate results is to store it somewhere, like a temptable.
create proc innerProc
as
insert #x (i) select count(*) from sysobjects
return
go
create proc outerProc
as
create table #x (i int not null )
exec innerProc
select * from #x -- or do whatever with the results...
return
go
exec outerProc
go
drop proc innerProc, outerProc
go
i
-----------
94
..this method works in that the outer proc can decide what to do with the results from innerProc now stored in the temptable..
If you want a resultset from the proc sometimes, and other times not, you must tell it which in code, perhaps by supplying a parameter that tells what to return.
Anyways, for nested procs to pass it's result (when >1 rows) outwards (not to the client) afaik the only method is to store it in a table (temp or permanent)
/Kenneth
August 10, 2005 at 6:52 am
Have you thought about openrowset.
Nigel Moore
======================
August 10, 2005 at 6:55 am
I don't see how openrowset would help in this case..?
(-- edit --)
Here's a great article from Erland that outlines the possibilites at hand..
How to share data between stored procedures.
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply