CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Dizzy49
Dizzy49

Reputation: 1520

Why doesn't this catch the error in the stored procedure?

SQL Server 2014 (v12.0.5546) - I have a "master" stored procedure that I run a bunch of others from. If one errors out, I want it to print a line stating so.

I ran it today, and one of the stored procedures in the master stored procedure returned an error saying that the table insert I was trying had the wrong number of variables, but it did not print the error.

I thought that stored procedures returned 0 if successful, so anything other than that would mean an error. Am I wrong, or is there a flaw in my process?

FYI, I don't need it to stop running if it encounters an error, I just need it to spit out the error message so I know where it failed. This is going to grow to 20-30 stored procedures by the time it's all said and done.

Here is the master stored procedure:

ALTER PROCEDURE [dbo].[Master_CreateLoadTables]
AS
    DECLARE @retval INT

    -- Builds all tables required for the stored procedures
    EXEC @retval = [BuildTables]

    IF (@retval) = 0
    BEGIN
        SET @retval = 1

        EXEC @retval = [Load_CustomerLookup]

        IF (@retval) <> 0
            PRINT 'Load of Customer Lookup Table Failed'

        EXEC @retval = [Load_CustomerInvoices]

        IF (@retval) <> 0
            PRINT 'Load of Customer Invoice Tables Failed'

        EXEC @retval = [Load_Payments]

        IF (@retval) <> 0
            PRINT 'Load of Payments Table Failed'
    END
    ELSE
        PRINT 'Table Creation Failed'

Upvotes: 0

Views: 953

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I thought that stored procedures returned 0 if successful, so anything other than that would mean an error. Am I wrong, or is there a flaw in my process?

Stored procedures return whatever value you tell them to. If there is no return statement, then they return success, 0.

The generally accepted practice is to return 0 for success and an error code for failure. But that is not enforced.

You are referring to user stored procedures. You need to investigate how they work in your environment.

I also encourage you to put the body of the stored procedure in a begin/end block.

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46203

one of the stored procedures in the master stored procedure returned an error saying that the table insert I was trying had the wrong number of variables, but it did not print the error.

It seems this was a compilation error. The return code will not be set after compilation errors so the assigned variable will remain unchanged. For example:

CREATE PROC dbo.ExampleCompilationError
AS
SELECT * FROM dbo.ThisTableDoesNotExist;
GO

DECLARE @rc int = NULL;
EXEC @rc = dbo.ExampleCompilationError;
SELECT @rc;
GO

The return code is still NULL with this code.

You could surround each proc execution with TRY/CATCH, which will catch compilation errors and execution time errors in the inner scope:

BEGIN TRY
    EXEC dbo.ExampleCompilationError;
END TRY
BEGIN CATCH
    PRINT 'ExampleCompolationError failed';
END CATCH;

Upvotes: 0

Related Questions