none
RETURN Function returns NULL

    Question

  • I have a stored procedure used in an ETL process.  The calling process expects a 1 or a 0 so it knows whether the procedure succeeded or failed.  1 indicates the procedure failed, 0 indicates the procedure succeeded.

    Here is the code for the procedure:

    ALTER PROCEDURE TestErrorCode
      @ReturnCode INT OUTPUT
    AS
    BEGIN
    
      BEGIN TRY
    
      SELECT @ReturnCode = ISNULL(@@ERROR, 1);
    
      IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[R_44]') 
        AND parent_object_id = OBJECT_ID(N'[dbo].[MART_INPATIENT_DIAGNOSIS]'))
        ALTER TABLE [dbo].[MART_INPATIENT_DIAGNOSIS2] DROP CONSTRAINT [R_44]
      
      END TRY
      BEGIN CATCH
    
      IF @@ERROR = 0
        SET @ReturnCode = 0
      ELSE
        SET @ReturnCode = 1;
    
      RETURN @ReturnCode
    
      END CATCH
    
    END

    The issue is that certain T-SQL errors cause the return code to be NULL instead of 1.  I tried changing the call to the procedure to change NULL to 1, but the other party is not accepting this solution.  Here is the EXECUTE command I wanted to use:

    DECLARE @RC int;
    DECLARE @ReturnCode int;
    
    EXECUTE @RC = [dbo].[TestErrorCode] @ReturnCode OUTPUT
    
    IF (@RC IS NULL) SET @RC = 1;
    
    SELECT @RC;

    How do I prevent NULL from being returned in this situation?

    Tuesday, November 26, 2013 9:56 AM

Answers

  • Hi Latheesh,

    I know by design try catch will not capture the fatal, But what about the nested try catches. (Just curious to know)

    I was trying this code:(It should not bubble up the error to outside try catch or it get eaten up inner try catch in the stored procedure)

    DECLARE @RC int;
    DECLARE @ReturnCode int;
    
    begin TRY
    EXECUTE @RC = [dbo].[TestErrorCode] @ReturnCode OUTPUT
    end try
    begin CATCH
    PRINT 'hello message'
    end catch 
    SELECT @ReturnCode ;
    


    Regards Harsh

    Tuesday, November 26, 2013 1:46 PM
  • Yes Harsh, outside try is handling the error with the procedure.

    DECLARE @RC int;
    DECLARE @ReturnCode int;
    
    begin TRY
    EXECUTE @RC = [dbo].[TestErrorCode] @ReturnCode OUTPUT
    Set @RC=0
    end try
    begin CATCH
      SET @RC = coalesce(nullif(@RC, 0), 1)
    end catch 
    Select @RC

    Tuesday, November 26, 2013 2:02 PM
  • Yup you are right , Try this two code: first failed, second using procedure got passed.

    First: 

    BEGIN TRY
        BEGIN TRY 
    	   PRINT 'This prints. First Try'
    	   SELECT * FROM doesnotexist
        END TRY
        BEGIN CATCH
    	   PRINT 'This does not print.First Catch'
        END CATCH
    END TRY
    BEGIN CATCH
        PRINT 'This does not print.Second Catch.'
    END CATCH

    Second Part: 

    CREATE PROCEDURE Test
    AS
    BEGIN
        BEGIN TRY 
    	   PRINT 'This prints. First Try'
    	   SELECT * FROM doesnotexist
        END TRY
        BEGIN CATCH
    	   PRINT 'This does not print.First Catch'
        END CATCH
    END
    
    BEGIN TRY
        EXEC dbo.Test
    END TRY
    BEGIN CATCH
        PRINT 'This does not print.Second Catch.'
    END CATCH

    I believe there is Throw function in Sql Server 2012, (My Installation is corrupt, need to do reinstallation). Does that bubble up in second case, Anyone want to try and share results?


    Regards Harsh

    Tuesday, November 26, 2013 2:19 PM

All replies

  • Why are you using the return code, and not your output variable @ReturnCode

    DECLARE @RC int;
    DECLARE @ReturnCode int;
    
    EXECUTE @RC = [dbo].[TestErrorCode] @ReturnCode OUTPUT
    
    SELECT @ReturnCode ;


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, November 26, 2013 10:08 AM
  • I still get NULL even when I select @ReturnCode instead of @RC.
    Tuesday, November 26, 2013 10:17 AM
  • Try the below:

    Drop table T2
    Drop table T1
    create Table T1(Col1 int Primary key)
    create Table T2(Col1 int references T1(Col1))
    
    Select OBJECT_NAME(parent_object_id) From sys.foreign_keys where object_id = 1005459787
    Go
    Alter PROCEDURE TestErrorCode
      @ReturnCode INT OUTPUT
    AS
    BEGIN
    
      BEGIN TRY
    
      SELECT @ReturnCode = ISNULL(@@ERROR, 1);
    
      IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'T2'))
        Begin
        ALTER TABLE [dbo].T2 DROP CONSTRAINT PK__T1__A259EE54213A230F --name is dummy
        Print 'Succes'
        End
      
      END TRY
      BEGIN CATCH
    	--Print 'fail'+ Cast(@@Error as varchar(100))
      IF @@ERROR = 0
        SET @ReturnCode = 0
      ELSE
        SET @ReturnCode = 1;
    
      RETURN @ReturnCode
    
      END CATCH
    
    END
    
    DECLARE @RC int;
    DECLARE @ReturnCode int;
    
    EXECUTE @RC = [dbo].[TestErrorCode] @ReturnCode OUTPUT
    
    SET @RC = coalesce(nullif(@RC, 0), @ReturnCode)
    
    SELECT @RC,@ReturnCode;

    Tuesday, November 26, 2013 10:29 AM
  • Ken

    I just ran your code as IS and got 0.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 26, 2013 10:31 AM
    Answerer
  • Hi Ken,

    I have tested the Olaf Code, It returning fine. No Null is returned for @ReturnedCode. 


    Regards Harsh

    Tuesday, November 26, 2013 10:32 AM
  • This is what I am getting when I run Olaf's code:

    Msg 4902, Level 16, State 1, Procedure TestErrorCode, Line 14

    Cannot find the object "dbo.MART_INPATIENT_DIAGNOSIS2" because it does not exist or you do not have permissions.

    -----------

    NULL

    (1 row(s) affected)

    Tuesday, November 26, 2013 10:56 AM
  • Hi Ken,

    Then i believe there is problem in your if exists constraint part, If it does not exists, It should not go in that flow.

    While altering you are altering object you are using object "MART_INPATIENT_DIAGNOSIS2" and in if exists you are using "MART_INPATIENT_DIAGNOSIS" without 2 at the end.


    Regards Harsh

    Tuesday, November 26, 2013 11:05 AM
  • Hi Ken,

    Then i believe there is problem in your if exists constraint part, If it does not exists, It should not go in that flow.

    While altering you are altering object you are using object "MART_INPATIENT_DIAGNOSIS2" and in if exists you are using "MART_INPATIENT_DIAGNOSIS" without 2 at the end.


    Regards Harsh

    I think I need to clarify something.

    The purpose of the procedure is to perform DDL operations in preparation for an ETL process.  My test case is to have the procedure attempt a DDL operation against a table that does not exist. 

    If the table does not exist:

    • The procedure should fail
    • Any DDL operations performed by the procedure up to the point of failure should be rolled back
    • The procedure should return a value of 1

    The code I have provided is testing this scenario.

    Tuesday, November 26, 2013 12:44 PM
  • Hello Ken,

    Try/Catch will not handle all kinds of errors. If the errors are fatal like non existence of table or objects, then it will not go to catch block. This is by design.

    Ref:https://connect.microsoft.com/SQLServer/feedback/details/739957/try-catch-should-always-work

    Tuesday, November 26, 2013 12:51 PM
  • Hello Ken,

    Try/Catch will not handle all kinds of errors. If the errors are fatal like non existence of table or objects, then it will not go to catch block. This is by design.

    Ref:https://connect.microsoft.com/SQLServer/feedback/details/739957/try-catch-should-always-work


    Ok, is there any other way to handle this error condition?  The client is insisting on a 0 or 1.
    Tuesday, November 26, 2013 12:53 PM
  • Hi Latheesh,

    I know by design try catch will not capture the fatal, But what about the nested try catches. (Just curious to know)

    I was trying this code:(It should not bubble up the error to outside try catch or it get eaten up inner try catch in the stored procedure)

    DECLARE @RC int;
    DECLARE @ReturnCode int;
    
    begin TRY
    EXECUTE @RC = [dbo].[TestErrorCode] @ReturnCode OUTPUT
    end try
    begin CATCH
    PRINT 'hello message'
    end catch 
    SELECT @ReturnCode ;
    


    Regards Harsh

    Tuesday, November 26, 2013 1:46 PM
  • Yes Harsh, outside try is handling the error with the procedure.

    DECLARE @RC int;
    DECLARE @ReturnCode int;
    
    begin TRY
    EXECUTE @RC = [dbo].[TestErrorCode] @ReturnCode OUTPUT
    Set @RC=0
    end try
    begin CATCH
      SET @RC = coalesce(nullif(@RC, 0), 1)
    end catch 
    Select @RC

    Tuesday, November 26, 2013 2:02 PM
  • So it looks like the only solutions are to handle the NULL return value somewhere in the call to the procedure.  All the suggestions I am seeing here are variations on that.  I will communicate this to my client.
    Tuesday, November 26, 2013 2:06 PM
  • Yup you are right , Try this two code: first failed, second using procedure got passed.

    First: 

    BEGIN TRY
        BEGIN TRY 
    	   PRINT 'This prints. First Try'
    	   SELECT * FROM doesnotexist
        END TRY
        BEGIN CATCH
    	   PRINT 'This does not print.First Catch'
        END CATCH
    END TRY
    BEGIN CATCH
        PRINT 'This does not print.Second Catch.'
    END CATCH

    Second Part: 

    CREATE PROCEDURE Test
    AS
    BEGIN
        BEGIN TRY 
    	   PRINT 'This prints. First Try'
    	   SELECT * FROM doesnotexist
        END TRY
        BEGIN CATCH
    	   PRINT 'This does not print.First Catch'
        END CATCH
    END
    
    BEGIN TRY
        EXEC dbo.Test
    END TRY
    BEGIN CATCH
        PRINT 'This does not print.Second Catch.'
    END CATCH

    I believe there is Throw function in Sql Server 2012, (My Installation is corrupt, need to do reinstallation). Does that bubble up in second case, Anyone want to try and share results?


    Regards Harsh

    Tuesday, November 26, 2013 2:19 PM