Thread: Error handing in MS SQL Server
05-05-04, 14:46 #1Registered User
- Join Date
- Oct 2003
Unanswered: Error handing in MS SQL Server
How can I get more specific information for an error using the @@ERROR
global variable in MS SQL Server?
Let's say I have a table with a FOREIGN KEY column defined on it AND another column which has a CHECK CONSTRAINT defined on it.
If any of these two constraints are violated, @@ERROR will contain the value 547. This does not tell me the actual object(s) that the error pertains to.
The error string returned does contain this information, but the messages are not very helpful for the average user. I don't think that parsing individual error message strings is the way to go, so does anyone have any ideas on how to proceed.
PS: Most of the time, I make all the necessary checks manually within the query and return custom error codes that are known within the client application. However, this causes some overhead since SQL Server checks everything all over again when I perform the actual INSERT or UPDATE.
Thanks in advance
05-05-04, 15:22 #2Window Washer
Originally Posted by afx2029
- Join Date
- Nov 2002
However, this causes some overhead since SQL Server checks everything all over again when I perform the actual INSERT or UPDATE.
When you define the object, define the object with a meaningful name.
Have the front end parse the message out and determine what to display. If it can't figure it out, then use the original message.
But you will definetly need a standard.
Cut and paste this to see what happens
USE Northwind GO CREATE TABLE myTable99( Col1 int IDENTITY(1,1) NOT NULL PRIMARY KEY , Col2 char(1)) GO CREATE TABLE myTable00( Col1 int NOT NULL , Col2 char(1) NOT NULL , CONSTRAINT myTable99_PK PRIMARY KEY (Col1, Col2) , CONSTRAINT myTable99_FK_myTable99_Col1 FOREIGN KEY (Col1) REFERENCES myTable99(Col1) , CONSTRAINT myTable99_CK_Col2_Y_N CHECK (Col2 IN ('Y','N'))) GO INSERT INTO myTable99(Col2) SELECT 'X' UNION ALL SELECT 'Y' UNION ALL SELECT 'Z' GO DECLARE @Error int, @RowCount int INSERT INTO myTable00(Col1, Col2) SELECT 4,'Y' SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT SELECT '@Error = ' + CONVERT(varchar(5),@Error), '@RowCount = ' + CONVERT(varchar(5),@Rowcount) INSERT INTO myTable00(Col1, Col2) SELECT 1,'X' SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT SELECT '@Error = ' + CONVERT(varchar(5),@Error), '@RowCount = ' + CONVERT(varchar(5),@Rowcount) GO DROP TABLE myTable00 DROP TABLE myTable99 GO