Programming in MS SQL Server 2012

@@ERROR

Error handling is an important part of any programming language. Error handling is used to display meaningful error message to database user and then terminates the program at the point of error that avoids data corruption due to abnormal termination.

Note: For Demo, create PRODUCT_ERROR table and then INSERT sample records though a stored procedure.

-- Example 126 --

CREATE TABLE PRODUCT_ERROR

(

Prod_Code char(4) Primary Key,

QTY_On_Hand int Not Null, CHECK(QTY_On_Hand > 0)

)

CREATE PROCEDURE dbo.usp_ProductError (@PCODE char(4) = NULL, @QOH int = NULL)

BEGIN TRANSACTION

BEGIN

INSERT INTO PRODUCT_ERROR VALUES(@PCODE, @QOH)

IF @@ERROR = 2627

BEGIN

PRINT 'PCODE is a primary key and thus cannot have duplicate values.'

Rollback Transaction

RETURN

END

ELSE IF @@ERROR = 515

BEGIN

PRINT 'Enter some values for PCODE or QOH, NULL not allowed.'

Rollback Transaction

RETURN

END

ELSE IF @@ERROR = 547

BEGIN

PRINT 'QOH Constraint violation has to be a positive value.'

END

COMMIT TRANSACTION

RETURN

Query Output Screen

SQL Server predefined error numbers and error messages are stored sysmessages system table that available in the master database. @@ERROR is a system function that used to retrieve particular error number while occur an error. It is option to display system error message or customize error message to database user or application.

The following Transact-SQL statements are used to INSERT records in PRODUCT_ERROR table. System occur constraint violation errror while execute third and fifth Transact-SQL statements.

-- Example 127 --

EXECUTE EBusiness.dbo.usp_ProductError 'P101', 100

EXECUTE EBusiness.dbo.usp_ProductError 'P102', 150

EXECUTE EBusiness.dbo.usp_ProductError 'P103', -100

EXECUTE EBusiness.dbo.usp_ProductError 'P104', 290

EXECUTE EBusiness.dbo.usp_ProductError 'P101', 100

Query Output Screen

* * * * *

Your Comment To AUTHOR