Reusing Stored Procedures results
-
Hi everybody!
The question is what options do I have when I want to reuse in a stored proc. another stored proc.'s result? And I want to do some nestings, too, meaning something like this:
I have, let's say StoredProc1, StoredProc2, StoredProc3,...StoredProcN, and in StoredProc2 I want to use StoredProc1's result, in StoredProc3 I want to use StoredProc2's result, and so on... in StoredProcN I want to use StoredProc(N-1)'s result.
Let's do a brainstorming! :)) I am open to any ideas, even if I might already know it/them.
Best regards,
Boti
-
You can use a temp table to retive the results into and use there (but can only return one resultset). Or OUTPUT variables if a fixed number of items return. Or you can setup a linked server to itself and call the SP thru the licked server with OPENQUERY (this uses an extra connection for each that calls thru when occurrs but does allow a single result set to be referenced like a table due to the way OPENQUERY handles)
-
We do this sort of thing on a regular basis and we use # temp tables as Antares suggests. If you need more than one set of results you can create another # table
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961 -
Unless you can return it as output parameters.
Andy
-
Ok. Thanx everybody for your help.
But I need some more ideas. The solution I am looking for must be one that is not affecting the code that is already written. So if I want to use an SP's result, I should not be forced to modify it's already existing code.
OpenQuery (or OpenRowSet) is the kind of thing I am looking for, the problem with that is, that when I am writing my code (that should use other SP's result, I don't know if on my clients server my application will have the permission to add a linked server, or not. In most of the cases will not. And even if it will, at some point I will be "forced" to write the password down in my code... that is not a healthy thing to do in my opinion.
So, I think we should continue. Some more ideas please! :))
Best regards,
Botond
PS: Can I use variable of TABLE data type as Output parameter. If yes, how? Examples please.
Edited by - botond.baika on 06/30/2003 12:10:00 AM
-
quote:
PS: Can I use variable of TABLE data type as Output parameter. If yes, how? Examples please.The answer is No, TABLE is not allowed as a parameter for an SP. However, if you are using SQL 2000 I would persue instead of reusable SP using a UDF instead to make truely reusable. And before you need to ask, you cannot call an SP inside a UDF. I do not think you will find a solution where you do not need to do some rewritting to create the results you need.
Edited by - antares686 on 06/30/2003 03:52:30 AM
-
Ok!
Thank you Antares for your help!
Best regards,
Botond
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply