Suppressing a result set 1

[bikerboy718]

I am not sure if this is possible but I was wondering if you can suppress a result set from a stored procedure.

Let me explain. I have 2 stored procedures SP1 and SP2 that both return back record sets. I have modified SP1 so that it calls SP2. When I run the modified version of SP1 it retuns back 2 record sets. It retuns back the record set from SP2 and the original result set from SP1. Is there a way that I can suppress the result set from SP2?

I have never tried to not display a result set so I have no idea where to start. Any help would be grealty appreciated.


























With Great Power Comes Great Responsibility!!!

Michael

 

[mrdenny]

Yes, create a temp table and execute sp2 into that table variable.

create procedure sp1 as
...
create table #sp2_output
([i]The Columns that sp2 outputs[/i])

insert into #sp2_output
exec sp2

drop table #sp2_output
...
go

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bikerboy718]

That did the trick. Thanks for your help.

With Great Power Comes Great Responsibility!!!

Michael

 

[ESquared]

Why don't you modify SP1 so it doesn't return a result set at all? Wouldn't that make more sense?

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[bikerboy718]

Not in this case. Currently SP1 and SP2 are being called by different applications. SP1 and SP2 almost do the the same thing. By using SP1 to call SP2 I am utiizing I am reusing a stored proc that works perfectly and also reducing the amount of code needed in SP1 to do the same thing. SP1 and SP2 returns 2 diffent record sets that are needed when called by the application. That is the only reason why I need to intercept the result set from SP2.

With Great Power Comes Great Responsibility!!!

Michael

 

[ESquared]

So add an optional parameter to SP1 that tells it to suppress the output. This won't affect the other calls to SP1 but you can tell it to avoid the resultset when you need to. You are killing performance by doing all the reading and writing of a useless INSERT Table EXEC statement.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]