Error Handling In An SP - 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

General SQL Server Forums

New to SQL Server Programming

Error Handling In An SP

Author

Topic

WeeBubba
Starting Member

18 Posts

Posted - 2007-01-05 : 21:35:02

hello. below is a very simple SP i have coded to insert a user into my database. i have created a unique index for the username column on my database table. if the username already i want to convey this to my C# code which is attempting the insert. i am using a combination of TRY/CATCH, RAISERROR and @@ERROR here to achieve my aim. could somebody please look at my code and tell me if the error handling code is OK, or is it overly complicated?

AS

SET NOCOUNT ON

DECLARE @Error int;

BEGIN

BEGIN TRY
INSERT INTO Users(
Username,
Password,
FirstName,
Surname,
DateBirth,
Email,
Id_Country)
VALUES(
@Username,
@Password,
@FirstName,
@Surname,
@DateBirth,
@Email,
@Id_Country)
END TRY
BEGIN CATCH

SELECT @Error = @@ERROR;

IF @Error = 2601
RAISERROR('The username already exists.', 15, 1)
ELSE
RAISERROR('An unknown error occurred.', 15, 1)

RETURN @Error;

END CATCH

SELECT @Id_User = SCOPE_IDENTITY()

END

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-06 : 01:52:18

I prefer not to have a duplicate key error, and test specifically for a duplicate. For example:

INSERT INTO Users(
	Username,
	Password, 
	FirstName, 
	Surname,
	DateBirth,
	Email, 
	Id_Country)
select
	@Username,
	@Password, 
	@FirstName, 
	@Surname,
	@DateBirth,
	@Email, 
	@Id_Country
where
	@Username not in 
	( select Username from Users )

if @@rowcount <> 1
	begin
	... do error processing ...
	end

Or like this:


if exists (select * from Users where @Username = Username )
	begin
	... do dup user error processing ...
	end
else
	begin


	INSERT INTO Users(
		Username,
		Password, 
		FirstName, 
		Surname,
		DateBirth,
		Email, 
		Id_Country)
	select
		@Username,
		@Password, 
		@FirstName, 
		@Surname,
		@DateBirth,
		@Email, 
		@Id_Country

	end

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources