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.
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);
EXECUTE dbo.SP_CreateTransactionLogEntry @ObjectId, @UniversalId, N'CREATE', @CreatingUser;
SELECT @ObjectId;
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?