Does Your SP Choke on Errors

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/doesyourspchockonerrors.asp

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • Hi Robert, good article, ive used this sort of technique with large data migration efforts and works well. I cant only dream for managed exception handling for t-sql in yukon 🙂

    Chris Kempster

    http://www.chriskempster.com

    Author of "SQL Server 2k for the Oracle DBA"


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Nice article. On a related note do you have any ideas on how to detect whether a stored procedure completed execution when run from ADO?

    For instance take the following SQL;

    CREATE TABLE TestTable (

    x INT NOT NULL

    CONSTRAINT CK_TestTable_x CHECK(x = 1)

    )

    GO

    CREATE PROCEDURE TestSP

    AS

    BEGIN

    BEGIN TRANSACTION

    INSERT INTO TestTable (x) VALUES (2)

    IF @@ERROR <> 0

    BEGIN

    ROLLBACK TRANSACTION

    RETURN -1

    END

    INSERT INTO TestTable (x) VALUES (1)

    IF @@ERROR <> 0

    BEGIN

    ROLLBACK TRANSACTION

    RETURN -2

    END

    COMMIT TRANSACTION

    RETURN 0

    END

    GO

    and the following VB program;

    Dim conn As Connection

    Dim cmd As Command

    Dim prm As Parameter

    Set conn = New Connection

    Set cmd = New Command

    Call conn.Open("File Name=c:\localhost.udl")

    cmd.CommandType = adCmdStoredProc

    cmd.CommandText = "TestSP"

    Set cmd.ActiveConnection = conn

    Set prm = cmd.CreateParameter("", adInteger, adParamReturnValue)

    Call cmd.Parameters.Append(prm)

    cmd.Execute

    Call MsgBox(prm.Value)

    conn.Close

    Set conn = Nothing

    Set cmd = Nothing

    ADO raises an error for the constraint violation, but actually the stored procedure handles it. To further confuse an error is not raised if the insert statements are swapped so it is the second that fails instead of the first.

    Any ideas?

  • If your store procedure fails before hitting a RETURN statement then the return value will be zero. This is the same value that you have used if your stored proc suceeds therefore your stored procedure needs to return a non-zero value to indicate success.

  • Robert,

    I would generally consider selecting from a non-existant table to be a bug rather than an error.

    In cases where it can be a legitemate programming error I would tend to use

    IF NOT EXISTS(SELECT 1 FROM SysObjects WHERE Type='U' AND Name = 'MyTable'

      BEGIN

        RAISERROR('MyTable is missing',11,1)

        RETURN 0

      END

  • I ran the following in Query Analyzer:

    CREATE PROCEDURE spTest1

    AS

    SELECT * FROM NoTable

    RETURN 1

    GO

    DECLARE @v-2 int

    exec @v-2 = spTest1

    PRINT @v-2

    And I got the following error:

    Server: Msg 208, Level 16, State 1, Procedure spTest1, Line 4

    Invalid object name 'NoTable'.

    From this I would say that if the execution of a stored procedure abruptly and completely halts then you will not get a return code, unless of course things happen differently with ADO.

    I'll say right now I know almost nothing about ADO. I also know very little VB. The closest I get to that is VB scripts in DTS packages. From my knowledge of SQL, it looks to me as if you should be able to get the return code from the SP as long as it doesn't halt as in the example above.

    Robert W. Marda

    SQL Programmer

    bigdough.com

    The world’s leading capital markets contact database and software platform.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • As for the non existant table, that was simply the first SP I came across that would stop on encountering the error and so I used it to illustrate a point in my article.

    I am glad you all liked the article. I am trying to provide examples (if nothing else) that I am not able to find in BOL to better illustrate how SQL Server handles stored procedures.

    Robert W. Marda

    SQL Programmer

    bigdough.com

    The world’s leading capital markets contact database and software platform.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • On the ADO error thing I tend to have a local stored procedure variable that I use to record errors that occur within the stored procedure.

    Obviously I try and test and trap the situation that may fail because letting it occur may result in a stopped procedure.

    I'll either have this as a straight "count the errors" type thing or as a bit map value so I can say "this error happened" AND "this error happened".

    I will either have this as the @@RETURN_VALUE parameter (the one passed back to VB by the SQL RETURN statement) or as a.n.other SQL OUTPUT variable.

    If there is some complicated logic within the stored procedures that would cause problems in flow then I try and break out that functionality into separate procedures.

    I've done some experimenting with the severity levels to see what fires off the VB error trapping routines. I don't have my results to hand but I think errors with severity 11 and above raise errors in the VB program that can be caught with the ON ERROR GOTO statement. I seem to remember that you get a generic ODBC error number + whatever message is actually returned from SQL.

  • I haven't run the procs as described in the article but the most troubling error handling I've had to deal with are errors like PK violations that abrubtly halt the entire batch that was sent by the client, in this case no matter how far you've nested the procs, the entire process is aborted and an error returned to the client, I would like to figure out how to trap these kinds of errors in TSQL and deal with them before my procs return control. For other ways of dealing with errors, see the master database and the SQL Server's own sp_ procs.

  • quote:


    I've had to deal with are errors like PK violations ... I would like to figure out how to trap these kinds of errors in TSQL and deal with them before my procs return control. For other ways of dealing with errors, see the master database and the SQL Server's own sp_ procs.


    To test for primary key violations I just use syntax like

    IF NOT EXISTS (SELECT 1 FROM MyTable WHERE MyField = @Value)

        BEGIN

            INSERT MyTable(My Field) VALUES (@Value)

            RETURN 1

        END

    ELSE

        BEGIN

            RAISERROR('My bespoke PK Error',10,1)

            RETURN 0

    END

    I raise bespoke errors simply because I want to raise them with a lower severity level than would cause an stoppage.

    Generally, most things can be trapped but the more error trapping is done at the SQL end the bigger the performance hit.

    If there are going to be multiple apps hitting a database then I put more error trapping in the database and middle tier. If there are very few apps then I expect the app to take the burden of the error trapping.

  • quote:


    Generally, most things can be trapped but the more error trapping is done at the SQL end the bigger the performance hit.

    If there are going to be multiple apps hitting a database then I put more error trapping in the database and middle tier. If there are very few apps then I expect the app to take the burden of the error trapping.


    Very true! But in a high transaction environment this is not always doable. I guess I've got a general gripe at the way SQL Server/T-SQL handles errors, because for the errors we've been talking about (and without using the trapping you've described), as stored procedure developers, we have to say 'we can trap some errors but not all..' given that, it splits the error trapping duties between the stored proc and the client/middle tier, when what we need at the stored proc level is a comprehensive trapping mechanism. The problem is even more complicated when you add in transaction handling as it's not always clear what was rolled back but that's another thread...

  • The problem I have is that Key violations do not terminate the current batch (unless XACT_ABORT IS set) But ADO does put a client application in the error handler.

    My problem with ADO is that I believe that it should only raise an error if the command execution was terminated. SP programmers would have to implement an error reporting using output variables similar to that specified by David above.

  • I don't think sp spStaticNoStopOnFirstError can continue if the first query run into an error.

  • I believe this code:

     
    
    IF NOT EXISTS (SELECT 1 FROM MyTable WHERE MyField = @Value)
    BEGIN
    INSERT MyTable(My Field) VALUES (@Value)
    RETURN 1
    END
    ELSE
    BEGIN
    RAISERROR('My bespoke PK Error',10,1)
    RETURN 0
    END

    is still flawed (for The Error trapping purpose that was intended to)

    Let's suppose we are in a highly transactional environment and two procedures are executing the same code. On the "Exists" clause both get the "false" but then one of them will insert the PK that maybe the other will try too! Then on the second procedure the insert will raise the "avoided" error to the client. You could have placed a lock but that would be even worse

    I would rather rewrite it as:

    
    
    INSERT MyTable(My Field) VALUES Where MyField <> @Value

    IF @@ROWCOUNT = 0
    RAISERROR('My bespoke PK Error',10,1)

    But in any case, the solution will really be:

    Microsoft, Please

    STRUCTURED EXCEPTION HALNDLING!!!!!!


    * Noel

  • spStaticNoStopOnFirstError will not continue as the table it refers to does not exist.

    However an sp will continue after a unique/primary key violation.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply