Programming in MS SQL Server 2012 |
@@ERRORError 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) ) GO CREATE PROCEDURE dbo.usp_ProductError (@PCODE char(4) = NULL, @QOH int = NULL) AS 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
END COMMIT TRANSACTION RETURN --
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 |
* * * * *