April 6, 2006 at 11:51 am
I have a stored procedure that calls a few other stored procedures which each return a single result set.
The main stored procedure when run, outputs multiple results, each coming from one of the nested stored procedures being called from within. At the end of the main stored procedure, I have a final big query that I want to return as the only result set.
How can I do this without rewriting the nested stored procedures to create output parameters(that code isn't under control)? My main stored procedure has SET NOCOUNT ON in the beginning.
I am using Query Analyzer from SQL 2000 SP4 but connecting to a SQL 7 db.
Thanks,
Erik
April 6, 2006 at 5:40 pm
You could store the results of the procedures in a table and select from that table..!!!
**ASCII stupid question, get a stupid ANSI !!!**
April 7, 2006 at 8:09 am
I tried what you suggested but I still receive the additional result set from the nested stored procedure.
CREATE PROCEDURE dbo.my_main_sp
@email Varchar(80)
AS
SET NOCOUNT ON
DECLARE @rc Char(3), @Message Varchar(100), @userid INT
--this sp returns a result set, not output parameters
EXEC dbo.my_nested_sp @email = 'test@test.com'
CREATE TABLE #Temp
(RC Char(3), Message Varchar(100), userid INT)
INSERT INTO #Temp
(RC, Message, pk)
Values (@RC, @Message,@userid)
SELECT * FROM #Temp
DROP Table #Temp
RETURN
April 7, 2006 at 8:36 am
Is this what you are looking for:
create procedure uspNest
as
select top 10 uid, status
from model.dbo.sysusers
go
create table #Tempo
(uid int,
status int)
insert into #Tempo -- This captures what uspNest returns
exec uspNest
select uid, status from #Tempo
drop table #Tempo
April 7, 2006 at 11:03 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply