Handling Errors with @@ERROR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Handling Errors with @@ERROR

Author

Topic

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-01-05 : 13:47:38

I have code in a stored procedure to handle errors by checking "IF @@ERROR <> 0". However my ASP.Net page displays an error message anyway when an error occurs.

I have a feeling that as soon as there is an error it is based back to my ASP.Net web page and the stored procedure does not get to to my IF line or something similiar to that.

Is there a way I can force the stored procedure not to raise an error automatically. I want to handle these using my IF block.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 13:57:56

Depending on the severity level of the sql error, the flow of execution may not make it past the error to reach your sql error handler. What you need to do is anticipate where one of these errors will occur and check for it before you attempt the action. So for instance if you want to delete a row that may be referenced by a foreign key, you should check for that before you attempt the delete. Or if you want to delete a table that may not exist, first check that the table exists.

Be One with the Optimizer
TG

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-01-05 : 14:12:18

The error is a violation of a constraint. I have the Zip code set to '[0-9][0-9][0-9][0-9][0-9]' and if that is violated it errors out my ASP.Net application.


-- Create a demographics entry for this application
INSERT INTO tblDemographics
(
    Name,
    PrimaryContact,
    PhoneNumber,
    Address,
    City,
    fkStateId,
    Zip,
    fkTerritoryId,
    fkAssignAcctExecId
)
VALUES
(
    @Name,
    @PrimaryContact,
    @PhoneNumber,
    @Address,
    @City,
    @fkStateId,
    @Zip,
    @fkTerritoryId,
    @fkAssignAcctExecId
);

-- Rollback all the transactions on any errors
IF @@ERROR <> 0
    BEGIN
        ROLLBACK;
        RETURN -1;
    END

If the ZIP code is wrong I would like my stored procedure to return -1 instead of raising an error back to the client.

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-01-05 : 14:14:17

on a side note, it would be rather cumbersome to have to check every constraint of every field for larger INSERT INTO statements that is one reason why I would rather just have a generic error message that something was not sent correctly.

jhermiz

3564 Posts

Posted - 2006-01-05 : 14:21:14

Stored procedures should be used to return data unless you are passing output parameters to them.
In any event, in your case you can include a simple IF condition and if it flags then return -1 else proceed with the insert.
I'm not sure I understand the problem, this type of checking is simple, especially if it is .net with a validator.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 14:27:12

check constraints and referencial integrety exist to protect the data (among other things) but a pleasant user experience and effective error handling will only happen with thorough validation of data. I personally think that should happen both client side as well as in the t-sql code. If the user tries to submit 'XXXXX' as a zip code, I don't want to rely on a call to the database and check constraint to catch that, that shouldn't even make out of the client. (MHO)

Be One with the Optimizer
TG

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-01-05 : 14:31:59

TG: I have a client-side checks as well, but in case something does go wrong I would still like the database to let me know.

This particular procedure creats a new application and once the application is in the database it returns the application number. If there was an error it returns -1

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 14:49:03

>>TG: I have a client-side checks as well, but in case something does go wrong I would still like the database to let me know.

That's good!

Here is one possible error handling model that will do what you want.

Another thing you can do is create a parameterized, custom message for each category of error types that your application would want to distinguish. ie: 50010 might be invalid parameter message, 50020 could be security access violations, etc...


declare @errMsg varchar(250)

-------------------------------------------
--validate parameters

--@Zip
if @zip is not null
begin
	if @zip not like '[0-9][0-9][0-9][0-9][0-9]' 
	begin
		set @errMsg = '''' + @zip + ''' is not a valid zip code'
		goto onError
	end
end
--------------------------------------------

--Body of SP

return 0
--------------------------------------------
onError:
if @@trancount > 0
	rollback tran

raiserror (@errMsg, 18, 1)
return -1

go

Be One with the Optimizer
TG

jhermiz

3564 Posts

Posted - 2006-01-05 : 15:51:40

Why are you duplicating a test that has already been handled though? To me that seems to be overkill, if your validator or your condition check on the client side states its bad input why is the database duplicating that operation?

To me its just additional overhead, I always here people say "But just in case..." just in case what ? If your code handles exactly what it needs to handle you shouldn't even question it.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 16:03:39

>>why is the database duplicating that operation?
Because as a database developer, I don't want to assume that only that 1 application is going to utilize all the great stored procedures I wrote. Maybe another application will be able share wealth, or maybe we want to convert some new data into the enterprise but we want to use the existing SPs to preserve the existing business logic. As far as I'm concerned, you can't "overkill" data validation. :)

Be One with the Optimizer
TG

jhermiz

3564 Posts

Posted - 2006-01-05 : 16:05:26

Right but for validation of a zip code ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 16:26:12

Yeah, if someone asks me to insert 'KissMyButt' as a zip code, I don't want insult my server, and rob precious milliseconds from valid requests, by blindly trying to insert it when I know perfectly well that a constraint will just need to be shaken out of bed to pull his ConstraintViolationError out of the frig and send it out. Just a zip code...Indeed! That's my address table mister and no one is going to violate it!

Be One with the Optimizer
TG

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-01-05 : 16:59:36

As for the duplication checks. While javascript validation on the client side web page is fine (ASP.Net creats Jscript code when you use those web controls) a user with bad intentions can EASILY circumvent the javascript restrictions on the page. Therefore it is always good to have checks in place in the database itself.

I view the Control Validators and other javascript/HTML restrictions and ways to gently remind the well intentioned users when they have made an honest mistake.

jhermiz

3564 Posts

Posted - 2006-01-05 : 18:41:04

quote:
Originally posted by Billkamm

As for the duplication checks. While javascript validation on the client side web page is fine (ASP.Net creats Jscript code when you use those web controls) a user with bad intentions can EASILY circumvent the javascript restrictions on the page. Therefore it is always good to have checks in place in the database itself.

I view the Control Validators and other javascript/HTML restrictions and ways to gently remind the well intentioned users when they have made an honest mistake.

Can you please post a valid control that has a validator that someone can circumvent, we can keep it on the subject and using a zip code would be a great example. I'd like to see how the validor can fail.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

jhermiz

3564 Posts

Posted - 2006-01-05 : 18:41:30

quote:
Originally posted by TG

Yeah, if someone asks me to insert 'KissMyButt' as a zip code, I don't want insult my server, and rob precious milliseconds from valid requests, by blindly trying to insert it when I know perfectly well that a constraint will just need to be shaken out of bed to pull his ConstraintViolationError out of the frig and send it out. Just a zip code...Indeed! That's my address table mister and no one is going to violate it!

Be One with the Optimizer
TG

That wont happen, the validator doesnt accept that.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-05 : 21:50:59

>>That wont happen, the validator doesnt accept that.
agreed, (if the sp is called from the app with the validator

)

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-06 : 01:48:26

Also See if this helps
http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-01-06 : 07:58:45

jhermiz: you don't have to use the web page provided to make requests to a server. You can write code to submit your own information to postback to a server.

jhermiz

3564 Posts

Posted - 2006-01-06 : 08:06:21

Thats only true if you don't secure the page providing credentials.
No one can sit on a specific .net project write a page and hope it posts back and hits the backend of a secure application.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-01-06 : 11:55:12

jhermiz you are assuming that all of your valid users are goign to behave accordingly.

In a coporation of 30,000+ people you can't assume that there isn't at least one employee that is upset and would love to do some damage. That employee might be a valid user to your site and have enough knowledge to save the ASP.Net page as HTML. Change ONE line:

var Page_ValidationActive = true;

to

var Page_ValidationActive = false;

And voila the page no longer validates.

jhermiz

3564 Posts

Posted - 2006-01-06 : 11:56:50

You dont have source control? Come on all these points can be easily countered. If an employee does that you know what happens...FIRED...or he / she cant because of source control.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources