存储过程返回错误的结果集 [英] Stored procedure returns the wrong result set

问题描述

我有一个存储过程，它调用了另一个嵌入的存储过程。该过程是从嵌入式过程返回结果集，我正在寻找一个简单的解决方案来阻止它。我知道我总是可以使用和输出参数，但我想知道是否有一些方法来改变过程语法而不必更改一堆代码来反映获得正确返回值的新方法。



基本上，该过程创建一个新的'Object'条目然后记录事务。



EXECUTE中的存储过程语句返回一个BIGINT，它是程序返回的，而不是所需的值。

I have a stored procedure that has a call to another stored procedure embedded. The procedure is returning the result set from the embedded procedure and I'm looking for a simple solution to stop this. I know that I can always use and output parameter but I would like to know if there is some way to alter the procedure syntax without having to change a bunch of code to reflect a new way to getting the correct return value.

Basically, the procedure creates a new 'Object' entry then logs the transaction.

The stored procedure in the EXECUTE statement returns a BIGINT which is what the procedure returns, not the desired value.

-- Insert
DECLARE @ObjectId INT;
DECLARE @UniversalId UNIQUEIDENTIFIER;
SET @UniversalId = NEWID();

INSERT INTO dbo.Object (ObjectTypeId, StatusId, UniversalId, DateCreated, LastModifiedDate, DisplayName)
VALUES (@ObjectTypeId, 1, @UniversalId, GETUTCDATE(), NULL, @DisplayName);
SET @ObjectId = CAST(SCOPE_IDENTITY() AS BIGINT); -- Value I want

-- Log transaction
EXECUTE dbo.SP_CreateTransactionLogEntry @ObjectId, @UniversalId, N'CREATE', @CreatingUser; -- Value I get

SELECT @ObjectId;

当我通过管理工作室执行此操作时，我看到两个单独的结果集。一个包含我想要的ObjectId，另一个包含来自第二个过程调用的TransactionID。 TransactionId是过程调用返回的值。



我尝试过不同的C＃调用。 SqlCommand.ExecuteReader（）和SqlCommand.ExecuteScalar（）都返回不需要的结果集。



总结一下，有没有办法在不使用的情况下返回正确的值OUTPUT参数？

When I execute this through the management studio, I see two separate result sets. One containing the ObjectId which I want and one containing the a TransactionID from the second procedure call. The TransactionId is the value returned by the procedure call.

I have tried different C# calls. Both SqlCommand.ExecuteReader() and SqlCommand.ExecuteScalar() return the undesired result set.

To sum up, is there a way to return the correct value without using an OUTPUT parameter?

推荐答案

实际上，答案很简单。 ExecuteReader（）是正确的调用，但是从调用SP中检索第一个表（结果集）。



所以，只需调用dr.NextResult（）来获取第二个返回数据表， DataReader.NextResult Method （Microsoft.VisualStudio.Data.Framework） [ ^ ]

Actually, the answer is very simple. ExecuteReader() is the correct call but that retrieves the first table (result set) from the call to the SP.

So, just call dr.NextResult() to get to the second table of returned data, DataReader.NextResult Method (Microsoft.VisualStudio.Data.Framework)[^]

这篇关于存储过程返回错误的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！