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.
