Hi guys,

Thank you for your time and help.



Here is a problem that I am facing. Consider the SQL code below. It's a simple procedure, raises an error, goes to catch block, that error gets re-raised, and I return -100 as return code. When I call this sp via SQL, I can caprure that return code and print it. All is very cool.



Based on that return code, I do want to build further client application logic. I would like to react to different values RETURNed in stored procedure.



However, my Deplhi counterpart programmer seems to have difficulty capturing that code.

He is saying that "If an exception is raised, the Return Value is unavailable"



He is saying that when I RAISERROR and cause an "exception" as he calls it, he has no chance of getting -100 return code. On my side, in SQL world, I seem to have no problem getting it.



Is this true? This is not an area of my expertize, i.e. SDAC, OLEDB, or other sort of database application interfaces.

I am not sure if this is so. It is hard for me to believe that such deficiency exist.



Please clarify this for me if you have such experience. Is there any way to get this RETURN code value after RAISERROR in the application layer?



Thanks again





Sample SQL code below to demonstrate an issue at hand.



SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO



IF OBJECT_ID('dbo.spAPP_utlRaiseError') IS NOT NULL

      AND OBJECTPROPERTY ( OBJECT_ID('dbo.spAPP_utlRaiseError') , 'IsProcedure' ) = 1

    DROP PROCEDURE dbo.spAPP_utlRaiseError

GO



CREATE PROCEDURE dbo.spAPP_utlRaiseError

AS

SET NOCOUNT ON

BEGIN TRY



    RAISERROR ('This is an error!', 16,1)



END TRY

BEGIN CATCH

    -- Call SQL Error handler

    --EXECUTE dbo.spAPP_utlSQLErrorHandler

    DECLARE @msg nvarchar(2000)

    SET @msg = ERROR_MESSAGE()

    RAISERROR(@msg, 16,1)



    RETURN -100

END CATCH;

GO



-- Now we call it and get return code, which should come accross as -100



declare @rc int

exec @rc = dbo.spAPP_utlRaiseError

print 'Return Value: ' + quotename(@rc )


-- No problems here!

go



IF OBJECT_ID('dbo.spAPP_utlRaiseError') IS NOT NULL

      AND OBJECTPROPERTY ( OBJECT_ID('dbo.spAPP_utlRaiseError') , 'IsProcedure' ) = 1

    DROP PROCEDURE dbo.spAPP_utlRaiseError

GO

Not familiar with Delphi, but familiar with Delphi developers. ;)

Try catching this error on the server, and only use RAISERROR for fatal
(unhandled) errors, but rather check the return value in T-SQL.


ML

---
http://milambda.blogspot.com/

thanks ML

Show quoteHide quote

Many client libraries are unable to digest the full rich diversity of SQL Server information streams.  Best practice is to return all error information to the client over a single channel, the RAISERROR.  Client's should not be using the return values (and either should server in 2005).

  Just cram the extra information into your error message.

  David

  Hi guys,

  Thank you for your time and help.



  Here is a problem that I am facing. Consider the SQL code below. It's a simple procedure, raises an error, goes to catch block, that error gets re-raised, and I return -100 as return code. When I call this sp via SQL, I can caprure that return code and print it. All is very cool.



  Based on that return code, I do want to build further client application logic. I would like to react to different values RETURNed in stored procedure.



  However, my Deplhi counterpart programmer seems to have difficulty capturing that code.

  He is saying that "If an exception is raised, the Return Value is unavailable"



  He is saying that when I RAISERROR and cause an "exception" as he calls it, he has no chance of getting -100 return code. On my side, in SQL world, I seem to have no problem getting it.



  Is this true? This is not an area of my expertize, i.e. SDAC, OLEDB, or other sort of database application interfaces.

  I am not sure if this is so. It is hard for me to believe that such deficiency exist.







  Please clarify this for me if you have such experience. Is there any way to get this RETURN code value after RAISERROR in the application layer?



  Thanks again





  Sample SQL code below to demonstrate an issue at hand.



  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO



  IF OBJECT_ID('dbo.spAPP_utlRaiseError') IS NOT NULL

        AND OBJECTPROPERTY ( OBJECT_ID('dbo.spAPP_utlRaiseError') , 'IsProcedure' ) = 1

      DROP PROCEDURE dbo.spAPP_utlRaiseError

  GO



  CREATE PROCEDURE dbo.spAPP_utlRaiseError

  AS

  SET NOCOUNT ON

  BEGIN TRY



      RAISERROR ('This is an error!', 16,1)



  END TRY

  BEGIN CATCH

      -- Call SQL Error handler

      --EXECUTE dbo.spAPP_utlSQLErrorHandler

      DECLARE @msg nvarchar(2000)

      SET @msg = ERROR_MESSAGE()

      RAISERROR(@msg, 16,1)



      RETURN -100

  END CATCH;

  GO



  -- Now we call it and get return code, which should come accross as -100



  declare @rc int

  exec @rc = dbo.spAPP_utlRaiseError

  print 'Return Value: ' + quotename(@rc )


  -- No problems here!

  go



  IF OBJECT_ID('dbo.spAPP_utlRaiseError') IS NOT NULL

        AND OBJECTPROPERTY ( OBJECT_ID('dbo.spAPP_utlRaiseError') , 'IsProcedure' ) = 1

      DROP PROCEDURE dbo.spAPP_utlRaiseError

  GO

Thanks David for your suggestion.
Due to SQL 2005 requiring to re-raise error message, it's already crammed in my case.
I hoped to get "cleaner" soltion: to get both of RAISERROR and RETURN.

I am looking for days when clients are "able to digest the full rich diversity of SQL Server information streams"
I guess I have no choice but to wait...
    Many client libraries are unable to digest the full rich diversity of SQL Server information streams.  Best practice is to return all error information to the client over a single channel, the RAISERROR.  Client's should not be using the return values (and either should server in 2005).

    Just cram the extra information into your error message.

    David

    Hi guys,

    Thank you for your time and help.



    Here is a problem that I am facing. Consider the SQL code below. It's a simple procedure, raises an error, goes to catch block, that error gets re-raised, and I return -100 as return code. When I call this sp via SQL, I can caprure that return code and print it. All is very cool.



    Based on that return code, I do want to build further client application logic. I would like to react to different values RETURNed in stored procedure.



    However, my Deplhi counterpart programmer seems to have difficulty capturing that code.

    He is saying that "If an exception is raised, the Return Value is unavailable"



    He is saying that when I RAISERROR and cause an "exception" as he calls it, he has no chance of getting -100 return code. On my side, in SQL world, I seem to have no problem getting it.



    Is this true? This is not an area of my expertize, i.e. SDAC, OLEDB, or other sort of database application interfaces.

    I am not sure if this is so. It is hard for me to believe that such deficiency exist.







    Please clarify this for me if you have such experience. Is there any way to get this RETURN code value after RAISERROR in the application layer?



    Thanks again





    Sample SQL code below to demonstrate an issue at hand.



    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO



    IF OBJECT_ID('dbo.spAPP_utlRaiseError') IS NOT NULL

          AND OBJECTPROPERTY ( OBJECT_ID('dbo.spAPP_utlRaiseError') , 'IsProcedure' ) = 1

        DROP PROCEDURE dbo.spAPP_utlRaiseError

    GO



    CREATE PROCEDURE dbo.spAPP_utlRaiseError

    AS

    SET NOCOUNT ON

    BEGIN TRY



        RAISERROR ('This is an error!', 16,1)



    END TRY

    BEGIN CATCH

        -- Call SQL Error handler

        --EXECUTE dbo.spAPP_utlSQLErrorHandler

        DECLARE @msg nvarchar(2000)

        SET @msg = ERROR_MESSAGE()

        RAISERROR(@msg, 16,1)



        RETURN -100

    END CATCH;

    GO



    -- Now we call it and get return code, which should come accross as -100



    declare @rc int

    exec @rc = dbo.spAPP_utlRaiseError

    print 'Return Value: ' + quotename(@rc )


    -- No problems here!

    go



    IF OBJECT_ID('dbo.spAPP_utlRaiseError') IS NOT NULL

          AND OBJECTPROPERTY ( OBJECT_ID('dbo.spAPP_utlRaiseError') , 'IsProcedure' ) = 1

        DROP PROCEDURE dbo.spAPP_utlRaiseError

    GO

Not sure about Delphi, but in the .NET world you can put the client-side database code in a TRY...CATCH style block and capture all of the relevant error information in a SqlException.  In that case the return value is irrelevant.  It does make sense that a return value might not be returned (or that you should not rely on the return value if it is) if a RAISERROR is the reason for the return on the server side.  What about putting your error message, -100 return code, and any other value you want returned in the actual error message using an XML style string that your counterpart can parse on the client side?  Something like this maybe:

RAISERROR ('<err> <ret>-100</ret> <msg>This is a test</msg> </err>', 17, 127)

  Hi guys,

  Thank you for your time and help.



  Here is a problem that I am facing. Consider the SQL code below. It's a simple procedure, raises an error, goes to catch block, that error gets re-raised, and I return -100 as return code. When I call this sp via SQL, I can caprure that return code and print it. All is very cool.



  Based on that return code, I do want to build further client application logic. I would like to react to different values RETURNed in stored procedure.



  However, my Deplhi counterpart programmer seems to have difficulty capturing that code.

  He is saying that "If an exception is raised, the Return Value is unavailable"



  He is saying that when I RAISERROR and cause an "exception" as he calls it, he has no chance of getting -100 return code. On my side, in SQL world, I seem to have no problem getting it.



  Is this true? This is not an area of my expertize, i.e. SDAC, OLEDB, or other sort of database application interfaces.

  I am not sure if this is so. It is hard for me to believe that such deficiency exist.



  Please clarify this for me if you have such experience. Is there any way to get this RETURN code value after RAISERROR in the application layer?



  Thanks again





  Sample SQL code below to demonstrate an issue at hand.



  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_NULLS ON

  GO



  IF OBJECT_ID('dbo.spAPP_utlRaiseError') IS NOT NULL

        AND OBJECTPROPERTY ( OBJECT_ID('dbo.spAPP_utlRaiseError') , 'IsProcedure' ) = 1

      DROP PROCEDURE dbo.spAPP_utlRaiseError

  GO



  CREATE PROCEDURE dbo.spAPP_utlRaiseError

  AS

  SET NOCOUNT ON

  BEGIN TRY



      RAISERROR ('This is an error!', 16,1)



  END TRY

  BEGIN CATCH

      -- Call SQL Error handler

      --EXECUTE dbo.spAPP_utlSQLErrorHandler

      DECLARE @msg nvarchar(2000)

      SET @msg = ERROR_MESSAGE()

      RAISERROR(@msg, 16,1)



      RETURN -100

  END CATCH;

  GO



  -- Now we call it and get return code, which should come accross as -100



  declare @rc int

  exec @rc = dbo.spAPP_utlRaiseError

  print 'Return Value: ' + quotename(@rc )


  -- No problems here!

  go



  IF OBJECT_ID('dbo.spAPP_utlRaiseError') IS NOT NULL

        AND OBJECTPROPERTY ( OBJECT_ID('dbo.spAPP_utlRaiseError') , 'IsProcedure' ) = 1

      DROP PROCEDURE dbo.spAPP_utlRaiseError

  GO

Select part of a string
UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string
parse out set of numbers from a column