Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Does Your SP Choke on Errors


Does Your SP Choke on Errors

Author
Message
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)

Group: General Forum Members
Points: 988 Visits: 82
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

ckempste
ckempste
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
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 Smile

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"
lgoodrich
lgoodrich
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
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?



David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3708 Visits: 3121
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

Newbie on www.simple-talk.com
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3708 Visits: 3121
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

Newbie on www.simple-talk.com
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)

Group: General Forum Members
Points: 988 Visits: 82
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

Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)SSC Eights! (988 reputation)

Group: General Forum Members
Points: 988 Visits: 82
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

David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3708 Visits: 3121
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

Newbie on www.simple-talk.com
dbdave
dbdave
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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.



David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3708 Visits: 3121
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

Newbie on www.simple-talk.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search