Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Does Your SP Choke on Errors Expand / Collapse
Author
Message
Posted Thursday, June 26, 2003 7:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 02, 2004 2:40 PM
Points: 15, 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...




Post #67706
Posted Thursday, June 26, 2003 8:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 06, 2012 4:41 AM
Points: 69, 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.




Post #67707
Posted Thursday, June 26, 2003 8:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2008 2:03 PM
Points: 180, Visits: 35
I don't think sp spStaticNoStopOnFirstError can continue if the first query run into an error.




Post #67708
Posted Thursday, June 26, 2003 8:34 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
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
Post #67709
Posted Thursday, June 26, 2003 8:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 06, 2012 4:41 AM
Points: 69, 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.




Post #67710
Posted Thursday, June 26, 2003 8:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #67711
Posted Wednesday, October 06, 2004 3:28 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 10, 2014 8:41 PM
Points: 9, Visits: 83
Nice, clean, concise examples.  Thank you


Regards,

Mike
Post #140404
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse