Nested SPs where the inner SP returns a recordset

Question

Post reply

Nested SPs where the inner SP returns a recordset

David F. Straker

Say Hey Kid

Points: 679
More actions
August 10, 2005 at 3:41 am

#89340

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.

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

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

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

David F. Straker Say Hey Kid Points: 679 More actions · Answer 2

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

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.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

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