SQL: User ERROR orUSER STORED PROCEDURE

--Create a table TEST1 having 2 field T_ID,T_NAME
CREATE TABLE TEST1
(
T_ID INT IDENTITY(1,1) PRIMARY KEY,
T_NAME VARCHAR(25)
)


--Create another table TEST2_DEPEND having 2 field TD_ID,TD_ADDRESS
GO
CREATE TABLE TEST2_DEPEND
(
TD_ID INT FOREIGN KEY REFERENCES TEST1(T_ID),
TD_ADDRESS VARCHAR(25)
)
GO

--Insert into TEST1 Values
GO
INSERT INTO TEST1 VALUES ('Manoj')
GO
INSERT INTO TEST1 VALUES ('Kumar')
GO
INSERT INTO TEST1 VALUES ('Bardhan')
GO

--User Stored Procedure For Insert/Delete (Pass 'D' for Delete,'I' for Insert)
--This Below defined procedure raiserror user defined error to user when some Error
--like Foreign Key Violation,Duplicate Key Entry (Primary key) or any other errors.
--I have first get the Error No. and then check the Error and Error No given in sys.messages message_id,language_id.
--Language id is defined in sys.syslanguages msglangid for us_english language(1033).ERROR_NUMBER() function is used
--to get the Error No. from the current session.

--I have used SQL Transaction for better accuracy of data in database .I used Begin Tran,ROllback Tran,Commit for
--implementing TCL structure in SQL Database.If any error occurs during execution the wrong data may not violate the
--database implemented rules.

GO
CREATE PROC USP_TEST1
(
@OP_TYP VARCHAR(3),
@ID INT,
@ADDRESS VARCHAR(30)=NULL
)
AS
BEGIN TRY
BEGIN TRAN
IF @OP_TYP='I'
BEGIN
INSERT INTO TEST1 VALUES(@ADDRESS)
END
ELSE IF @OP_TYP='D'
BEGIN
DELETE FROM TEST1 WHERE T_ID=@ID
END
COMMIT
END TRY
BEGIN CATCH
DECLARE @TMP VARCHAR(MAX)
SET @TMP=ERROR_NUMBER();
IF @TMP=547
BEGIN
--RAISERROR raise Error For user (If Any)
RAISERROR('Cannot Delete! Value Dependency (Foreign key)??? ',16,1)
--Transaction Rollback for keeping only Old Data
ROLLBACK TRAN
END
ELSE IF @TMP=2627
BEGIN
RAISERROR('Cannot Insert! Cannot Insert Duplicate Value ???',16,1)
--Transaction Rollback for keeping only Old Data
ROLLBACK TRAN
END
ELSE
BEGIN
--RAISERROR raise Error For user (If Any)
RAISERROR('Some Error in DataBase??',16,1)
--Transaction Rollback for keeping only Old Data
ROLLBACK TRAN
END
END CATCH
GO



--Insert value Into Foreign key Table Value
INSERT INTO TEST2_DEPEND VALUES(2,'Bhubaneswar')

--Execute the Procedure For Delete From Primary Table(D=Delete,I=insert)
EXEC USP_TEST1 'D',2

--Expected Output Error

(0 row(s) affected)
Msg 50000, Level 16, State 1, Procedure USP_TEST1, Line 26
Cannot Delete! Value Dependency (Foreign key)???




I have used all this type of error handling error for the developers which will not hamper the frontend developer to handle error from
application frontend.For better knowledge about Errors & Languages in SQL Follow the below lines.


SELECT * FROM SYS.MESSAGES

SELECT * FROM SYS.SYSLANGUAGES


Posted By:-javadevelopersguide
Link:-http://javadevelopersguide.blogspot.com