Does Your SP Choke on Errors

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Robert Marda » Does Your SP Choke on Errors

17 posts, Page 1 of 2

1 2 »»

Does Your SP Choke on Errors

Rate Topic

Display Mode

Topic Options

Author

Message

Robert W Marda

Posted Saturday, June 21, 2003 12:00 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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

Robert W. Marda
SQL Programmer
Ipreo

Post #13448

ckempste

Posted Thursday, June 26, 2003 12:33 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1

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
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"

Post #67697

lgoodrich

Posted Thursday, June 26, 2003 1:29 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, June 06, 2012 4:41 AM
Points: 69, Visits: 37

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?

Post #67698

David.Poole

Posted Thursday, June 26, 2003 2:16 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 2,766, Visits: 1,441

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.

LinkedIn Profile

Post #67699

David.Poole

Posted Thursday, June 26, 2003 2:25 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 2,766, Visits: 1,441

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

LinkedIn Profile

Post #67700

Robert W Marda

Posted Thursday, June 26, 2003 6:30 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

I ran the following in Query Analyzer:

CREATE PROCEDURE spTest1
AS

SELECT * FROM NoTable

RETURN 1
GO

DECLARE @v int

exec @v = spTest1

PRINT @v

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
SQL Programmer
Ipreo

Post #67701

Robert W Marda

Posted Thursday, June 26, 2003 6:35 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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
SQL Programmer
Ipreo

Post #67702

David.Poole

Posted Thursday, June 26, 2003 7:16 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 2,766, Visits: 1,441

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.

LinkedIn Profile

Post #67703

dbdave

Posted Thursday, June 26, 2003 7:18 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 02, 2004 2:40 PM
Points: 15, Visits: 1

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.

Post #67704

David.Poole

Posted Thursday, June 26, 2003 7:30 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 2,766, Visits: 1,441

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.

LinkedIn Profile

Post #67705

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions