Prev: Manipulation of date ranges
Next: Need help reading my DBCC MEMORYSTATUS
From: Paul Ilacqua on 24 Apr 2010 09:35
As a rule of thumb, is it better to let the DB engine throw an
error back to the caller, or do you do it in your SP code? In the case of a
user already existing in a table, should I check that at the start of my
SP, or let the Engine catch it as a PK violation and send the message to to
client?
Always looking to improve my code... I bet you this is one of those ... It
depends .......
Thanks
Paul

From: Dan Guzman on 24 Apr 2010 10:17
> As a rule of thumb, is it better to let the DB engine throw an error back
> to the caller, or do you do it in your SP code? In the case of a user
> already existing in a table, should I check that at the start of my SP,
> or let the Engine catch it as a PK violation and send the message to to
> client?

It's generally best to check for anticipated error conditions rather than
relying on a constraint violation for errors that you anticipate. The below
RAISERROR example also returns a non-zero return code to the caller.

> Always looking to improve my code... I bet you this is one of those ... It
> depends .......

You are right that "it depends", though. If the application has previously
verified that the user doesn't exist, a duplicate insert attempt will be
rare (i.e. race condition) so you can simplify your code and let SQL Server
throw the error in the event of a constraint violation or other unexpected
error. Be aware that if you use TRY/CATCH, you must use RAISERROR to throw
the error back to the caller.

CREATE PROC dbo.usp_InsertUser
@UserID int,
@UserName varchar(30)
AS,
IF EXISTS(SELECT * FROM dbo.Users WHERE UserID = @UserID)
BEGIN
RAISERROR('Specified UserID already exists', 16, 1);
RETURN 1;
END

INSERT INTO dbo.Users(UserID, UserName)
VALUES(@UserID, @UserName);

RETURN @@ERROR;
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Paul Ilacqua" <pilacqu2(a)twcny.rr.com> wrote in message
news:10E983DD-3C73-42A0-ADF3-29F86945104E(a)microsoft.com...
> As a rule of thumb, is it better to let the DB engine throw
> an error back to the caller, or do you do it in your SP code? In the case
> of a user already existing in a table, should I check that at the start
> of my SP, or let the Engine catch it as a PK violation and send the
> message to to client?
> Always looking to improve my code... I bet you this is one of those ... It
> depends .......
> Thanks
> Paul
>
From: Paul Ilacqua on 24 Apr 2010 11:59
Thanks Dan for the speedy reply...

"Paul Ilacqua" <pilacqu2(a)twcny.rr.com> wrote in message
news:10E983DD-3C73-42A0-ADF3-29F86945104E(a)microsoft.com...
> As a rule of thumb, is it better to let the DB engine throw
> an error back to the caller, or do you do it in your SP code? In the case
> of a user already existing in a table, should I check that at the start
> of my SP, or let the Engine catch it as a PK violation and send the
> message to to client?
> Always looking to improve my code... I bet you this is one of those ... It
> depends .......
> Thanks
> Paul
>

 | 
Pages: 1
Prev: Manipulation of date ranges
Next: Need help reading my DBCC MEMORYSTATUS