August 12, 2004 at 12:53 am
There is a missing resultset when calling a stored procedure with a
nested stored procedure.
=========================
CREATE PROCEDURE usp_AddTen
@nResult int
AS
SET NOCOUNT ON
SET @nResult = @nResult + 10
SELECT @nResult AS Total
GO
=========================
=========================
CREATE PROCEDURE usp_ProcessTotal
AS
SET NOCOUNT ON
EXECUTE usp_AddTen 123
SET @nDog = 1000
SELECT @nDog AS MyTestOutput
GO
=========================
When I call a stored procedure with a nested stored procedure via the ADO
Recordset Object, I only get the first resultset back. For instance, if I
call usp_ProcessTotal it will only return a single record and in this case I
will get a column named "Total' with a 133 value. The recordset output for
usp_ProcessTotal is missing, in particular, "MyTestOutput" doesn't exist as a
recordset when called from
The strange thing is that is I call the usp_ProcessTotal from the Query
Analyzer, it will return two recordsets, the first one is a column named
"Total" with 133 in it and the second recordset is "MyTestOutput" with 1000
in it. The Query Analyzer output contains two recordsets.
This isn't a SET NOCOUNT ON issue or a get/move to next record issue. The
recordset for MyTestOutput doesn't seem to exist when calling nested stored
procedure (or variations on this example like calling extended stored
procedures within a main stored procedure). In other words, there is only a
single resultset returned to the client. If I comment out the nested sp call
to usp_AddTen, then I will get the missing recordset "MyTestOutput" with 1000
in it. For some reason, a nested stored procedure call fails to obey the SET
NOCOUNT ON or to allow a multiple resultset be returned from the main calling
stored procedure.
Normally, I would not use a recordset as output and use OUTPUT variables
instead to solve the problem but this is not possible for our application
because the ADO Command Object does not exist in our
CEFusion, etc.) and we are precluded from ever using OUTPUT variables.
Is there a method to tell SQL Server 2000 (SP3) that I have no interest in
the nested stored procedure resultset and only want the last SELECT statement
in usp_ProcessTotal to return a recordset?
How can I suppress nested stored procedure resultset or find a solution that
will properly return multiple recordsets to the client?
Any solutions for the missing resultset when calling a stored procedure with
nested stored procedures?
August 12, 2004 at 8:55 am
In your code you're actually looking for two distinct recordsets, right? Not both results returned in a single recordset. Basically using the NextRecordset method...
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply