CODEWITHSUNDEEP
sql-server-2008try-catchreturn-value
Doctor Chris Chris
Doctor Chris Chris

Reputation: 211

SQLServer 2008 - Return Value of stored procedure after catching an exception

Given the following SP ...

CREATE PROCEDURE [dbo].[trytest1]

AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT 'Testing 1'

DECLARE @int1 INTEGER
DECLARE @int2 INTEGER
DECLARE @int3 INTEGER

SET @int1 = 0
SET @int2 = 0

    BEGIN TRY
        SELECT 'Testing 2'

        SET @int3 = @int1 / @int2

        SELECT 'Testing 3'
    END TRY

    BEGIN CATCH
        SELECT 'Testing 4'
    END CATCH

SET @int3 = @int1 + @int2
SELECT 'Testing 5'

Executing this gives ...

Testing 1

Testing 2

Testing 4

Testing 5

Return Value -6

Catch catches divide by zero, but execution continues after catch block Im curious why even though query executed successfully and continued after the trycatch normally, the return value is -6. Will it stay -6 forever?

And, while I'm here, just curious, is it possible to continue execution in Try Block (ie select 'Testing 3')

Thanks

Upvotes: 1

Views: 2106

Answers (1)

Diego
Diego

Reputation: 36156

that's the standard behavior on all exception handling mechanisms. You have a TRY and a CATCH and code in between. If you have an exception in the code, it stops from running and go to the CATCH block.

If you want the SELECT 'Testing 3' to be executed, it must be placed outside the TRY...CATCH block.

If you dont want to execute anything else on the proc, you either put everything on the TRY...CATCH block OR exists the execution on the catch block

Upvotes: 1

Related Questions