Return Error Number in Procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

Return Error Number in Procedure

Author

Topic

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-07 : 14:16:10

I have a table and Procedure with the following structure.

CREATE TABLE Users
(
ID int IDENTITY(1,1) Primary Key,
FirstName varchar(32) NOT NULL,
MI char(1) CONSTRAINT CK_mi CHECK (mi like '[A-Z]'),
LastName varchar(32) NOT NULL,
UserName varchar(32) NOT NULL UNIQUE,
Password varchar(32) NOT NULL,
Address1 varchar(32),
Address2 varchar(32),
City varchar(32),
StateID int references States(ID),
ZipCode varchar(10)
CONSTRAINT CK_zip_code CHECK (ZipCode like
'[0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]' OR
ZipCode like '[0-9][0-9][0-9][0-9][0-9]'),
RoleID int NOT NULL DEFAULT 4 references Roles(ID),
Date_Created datetime NOT NULL DEFAULT getdate(),
Created_By int NOT NULL DEFAULT 1 references Users(ID)
)
GO

CREATE PROCEDURE usp_create_user
@FirstName varchar(32),
@MI char(1),
@LastName varchar(32),
@UserName varchar(32),
@Password varchar(32),
@Address1 varchar(32),
@Address2 varchar(32),
@City varchar(32),
@StateName varchar(32),
@ZipCode varchar(10),
@RoleName varchar(32),
@CreatorName varchar(32)
AS
SET NOCOUNT ON
DECLARE @StateID int
DECLARE @RoleID int
DECLARE @CreatorID int
DECLARE @Results int

BEGIN
SELECT @StateID=ID
FROM States
WHERE StateName = @StateName

SELECT @RoleID=ID
FROM Roles
Where RoleName=@RoleName

SELECT @CreatorID=ID
FROM Users
WHERE UserName = @CreatorName

IF @CreatorID is Null
BEGIN
SET @CreatorID = @RoleID
END
END

INSERT INTO users
VALUES (@FirstName, @MI, @LastName, @UserName, @Password, @Address1, @Address2, @City, @StateID, @ZipCode, @RoleID, getdate(), @CreatorID)
SET NOCOUNT OFF

SET @Results = @@ERROR

RETURN @Results
GO

When I try to insert a UserName that already exists in the table, I get back the full error message instead of just the error number. Is there a way to return just the error number to the front end app? Secondly, is the reason I receive the full error message because I am using Query Analyzer to debug?

Jeremy

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-07 : 14:25:46

Yes, QA will give you the whole message, but using an ADO Command object will let you retrieve just the @@ERROR return value.

Why not perform a check before doing the INSERT, and then use RAISERROR to craft your own message?

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-07 : 14:32:06

I simply want to know whether the insert was sucessful or not. Are there different error messages for different constraint errors? For example, if I violate the UNIQUE constraint will I receive Msg 2627 everytime? Or if I violate a Check Constraint will I receive Msg 547?
One final questions, is there a function that will return the name of the violating constraint?

Thanks for the quick reply robvolk!

Jeremy

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-07 : 16:29:42

Usually the error message will list the name of the violated constraint, but the only function that I know of that has error info is @@ERROR. NOT NULL constraints aren't usually named, but the column name will appear in the error message. There's some advanced error reporting techniques here:

http://www.nigelrivett.com/

I'm not a big believer in generic error handling, and whenever I want or need specific "error" messages or handling, I'll add logical tests for those conditions and use RAISERROR to provide a custom message. Part of it is the same reason you stated: you want to know EXACTLY what caused the error, especially if more than one thing went wrong. I laid out my opinion here:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13617

It's a cranky ol' bastard approach to error handling

but that's how I approach it. If you know something might go wrong, and you can takes steps to avoid, prevent, or properly manage it, it is not ERROR handling, but rather a fully thought-out process that handles CONTINGENCIES and CONDITIONS. Nor is it redundant or a waste of time to add these kinds of checks even though the software can handle the "error".

IMHO an error is something that is completely unanticipated, like September 11th. I'm not trying to be shocking or absurdly funny when I say that. There were already steps in place to ensure guns did not get on a plane, but little or nothing about knives, and it led to disaster. So they started checking for knives too. As it turns out, it STILL wasn't enough, and some guy got a bomb in his shoe on a plane. Then they added more steps to ensure THAT couldn't happen again either, and you now have shoe searches! The point is, it's no longer a question of handling an "error"; it's now part of a process to handle contingencies.

In framing it in those terms, I would rather take explicit steps to handle forseen contingencies than to rely on generic error handling, wouldn't you?

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-07 : 16:52:56

Thanks for the thoughts robvolk. After reading your post, I totally agree with you. The point is well made, especially considering the code I provided deals with user security. In hind site, using constraints keeps you database as clean as possible, but contingencies need to be taken into consideration for know problems. If I thought enough to put a constraint, I can put a little more effort in to building customized error messages on the database side.

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources