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)

)

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


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



* * * * *


Email Your Comment To AUTHOR