SQL Clone
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
dbdave
dbdave
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 1
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...



lgoodrich
lgoodrich
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 37
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.



sunjiulu
sunjiulu
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 35
I don't think sp spStaticNoStopOnFirstError can continue if the first query run into an error.



noeld
noeld
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22442 Visits: 2048
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
lgoodrich
lgoodrich
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 37
spStaticNoStopOnFirstError will not continue as the table it refers to does not exist.

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



Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2889 Visits: 113
sunjiulu:

When I execute spStaticNoStopOnFirstError in the pubs database on SQL Server 2000. Both statements in the SP execute even though the first query fails because of a constraint on the table. The second query will succeed the first time it is run. After that it will fail unless you delete all the rows it inserted. In any case, for me both queries run every time I execute this SP.

Are you getting a different reaction when you execute this SP?

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
Mike Schmidt
Mike Schmidt
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 83
Nice, clean, concise examples. Thank you


Regards,

Mike
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