0

suppose:

CREATE PROCEDURE [SPROC1] 

AS
BEGIN   
    BEGIN TRY
        BEGIN TRANSACTION   
        -- do some stuff        
        COMMIT TRANSACTION 
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION ;
        -- May have SELECT "Error Message XYZ" as ErrorMessage;
        RETURN -5
    END CATCH   

    SELECT * FROM MyTable;
    RETURN 1
END
GO

If in another SPROC, I need to check that return value AND iterate the result set, how do I do it?

This will return both in SSMS, but does not allow me to capture the result set for further consumption.

DECLARE @rValue int
EXEC    @rValue = SPROC1
SELECT  'Return Value' = @rValue

And what if there are multiple SELECTs coming from SPROC1?

JoeBrockhaus
  • 2,745
  • 2
  • 40
  • 64

1 Answers1

2

Your best bet is to just return a single result set per stored procedure. Then if you need to traverse the data in a calling sproc you can use techniques such as are documented in this stackoverflow entry.

If you absolutely need to include a return value, you could consider adding it as a row or additional column in your result set. Or even better would be to calculate the "return value" in the calling sproc based on the result set if that is possible (i.e. no records, status of records, etc.). Unfortunately I've not come across a better option.

Community
  • 1
  • 1
Madison
  • 411
  • 5
  • 14
  • I generally agree with your answer. I've never come across a situation where I've absolutely needed to do this. Multiple sets was a future-proofing (or busting) consideration. In trying to advise on a standard for SPROC consumption in an application, this metaphor was posited - basically trying to leverage all capabilities of the SPROC as a test. In a .NET application, you can get multiple result sets and the return value. – JoeBrockhaus Feb 12 '15 at 17:34
  • If there isn't an obvious solution to this *without* having to use out params, TVF, etc (aka change the architecture), then obviously this is the wrong approach, and I'm perfectly OK with that. Proofing this out can be done in another layer of the application (and probably better suited as Unit tests anyway) – JoeBrockhaus Feb 12 '15 at 17:42