EXEC Stored_Procedures Resultsets

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

EXEC Stored_Procedures Resultsets

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-12-03 : 06:08:03

Hi,

I got a stored procedure that calls another stored procedure, like this:

--stored procedure sp_tasks--
EXEC @Print = bchLabelPrint
EXEC ...
EXEC ...

The thing is, I want this stored procedure to return just 1 return value (results). But if I use these EXEC commands, it gives a return value for every sub stored procedure.

How to stop this? Please help it is urgent.

Best regards, Joris

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-03 : 06:40:20

I'm struggling to understand without more information, but let's try this for a first stab...

create proc MybchLabelPrint as
return cast(rand() + 0.5 as int)
go

create proc Mysp_tasks as
declare @Print int
declare @t table (id int identity(1, 1), [Print] int)
exec @Print = MybchLabelPrint; insert @t select @Print;
exec @Print = MybchLabelPrint; insert @t select @Print;
exec @Print = MybchLabelPrint; insert @t select @Print;
select * from @t
go

exec sp_tasks

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-12-03 : 06:50:39

quote:
Originally posted by RyanRandall

I'm struggling to understand without more information, but let's try this for a first stab...

create proc MybchLabelPrint as
return cast(rand() + 0.5 as int)
go

create proc Mysp_tasks as
declare @Print int
declare @t table (id int identity(1, 1), [Print] int)
exec @Print = MybchLabelPrint; insert @t select @Print;
exec @Print = MybchLabelPrint; insert @t select @Print;
exec @Print = MybchLabelPrint; insert @t select @Print;
select * from @t
go

exec sp_tasks

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Thanks, but my stored procedures return querys, not variables, it is more like

[code]create proc MybchLabelPrint as
select cast(rand() + 0.5 as int) as Calc
go

create proc Mysp_tasks as
exec mybchLabelPrint

And when I do that, it will give all sub querys in the result set of the tasks stored procedure...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-03 : 07:07:24

I thought as much - but it was difficult to tell. How's this...?

create proc MybchLabelPrint as
select cast(rand() + 0.5 as int) as Calc
go

create proc Mysp_tasks as
declare @t table (id int identity(1, 1), [Print] int)
insert @t exec MybchLabelPrint;
insert @t exec MybchLabelPrint;
insert @t exec MybchLabelPrint;
select * from @t
go

exec sp_tasks

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources