Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kasz
Starting Member
3 Posts |
 Posted - 2004-09-14 : 09:12:39
|
| Hi there,How can you stop a results set from a nested sproc frompassing through to the client or calling sproc?For example, consider 2 sprocs, sproc X and sproc Y where Y calls X.X has 2 SELECT statements so X returns 2 results sets. How does Y stop both results sets from X passing through to the client (or calling sproc) and instead only allow the chosen results set to pass through?Both sprocs are on the same server in the same database. I am using SQL Server 2000.I believe one way is to use OPENQUERY but is there very simple way?Thanks in advance.Kas. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-14 : 09:20:49
|
| You can add a paramter to X that allows you to toggle on/off the returning of the recordsets. There's probably an easier way, but that's one idea.- Jeff |
 |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-09-14 : 09:20:55
|
| If no result sets need to be passed to the client, don't call the stored procedure with the select commands! |
|
|
|
kasz
Starting Member
3 Posts |
Posted - 2004-09-14 : 09:48:02
|
| Problem I have is that I need to run the existing sproc in a new sproc of mine as the existing sproc returns a couple of values I need to use in my sproc. However, the existing sproc also returns a results set (in addition to its output parameters) which is what I want to prevent from passing back through to the client.I can, as suggested, modify the existing sproc but was wondering if there was a simple way to stop results sets from sprocs from passing through.Cheers.Kas. |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-09-14 : 10:48:59
|
| Can't you pull the sql code out of the stored procedure that you need? You could probably refactor this common code into a new stored procedure that you and the old stored procedure would call. |
|
|
|
kasz
Starting Member
3 Posts |
Posted - 2004-09-14 : 10:57:37
|
| There are a number of things I could do to work around the problem but that's not what I'm actually asking here. All I want to know is if there is a simple way to prevent results sets from a nested sproc from being passed through to the calling sproc or client app. In my case, as explained, I have a sproc which returns output values and a results set and for simplicity it would be nice to not have to change anything in the existing sproc and instead be able to call it and prevent the results set from passing through. I just want to use the output values. Is there any way to do that? |
|
|
|
|
|
|
Prevent results set from a nested sproc ...