Multiple SP values overriding the desired SP value

I have a stored procedure (lets call him Earl to keep things clear) that executes another sp (lets call him Daryl). Daryl returns a value, and although the last thing the first Earl does is selects a record set to return to the code, the only thing the code can access is values from Daryl. I am using NOCOUNT ON in the beginning of Earl, and NOCOUNT OFF at the end of Earl, but I still get Daryl's values.

I even put NOCOUNT ON / OFF in Daryl, but no such luck. How do I bypass Daryl's values and get to Earl's?

Thanks.

Could you post your code?|||

NOCOUNT only effects the rowcount (as in the message (n row(s) affected), and the global @.@.rowcount) not the actual returning of values.

So, are you saying the rowcount is being updated even with SET NOCOUNT ON ?

|||

no, nothing to do with the row count I guess.

What it is: because the secondary SP, which is called by the first, uses a select statement, that data generated from the select is being returned from the primary SP, and not the intended data.

Here is a boiled down version of the SP:

alter procedure earl
as
begin

insert into table (x, y, z) values ('a', 1, 'c')
@.id = @.@.identity

exec daryl(@.id )

select * from table where id = @.id

end

alter daryl
@.id int
as
begin
insert into joiningtable (x, y, z) values (@.id, 1, 'c')

@.error = @.@.error

if(@.error > 0)
select @.error as result
else
select @.@.identity as result

end

so the output from Earl is @.@.identity from Daryl, and not "select * from table where id = @.id " from Earl.

Daryl needs to return data, because this SP can be called directly from another function if @.id is known.

|||Earl will return the intended records in the second result set. So you can try to reach the Next resultset.|||

Yes, this will return two result sets.

What are you using on the client side to recieve these results? If try this from sqlcmd\osql\SSMS, you will see both result sets.