- A stored procedure is a saved collection of Transact-SQL statements or a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters.
- Procedures can be created for permanent use or for temporary use within a session, local temporary procedure, or for temporary use within all sessions, global temporary procedure.
- Stored procedures can also be created to run automatically when an instance of SQL Server starts.
Syntax:
CREATE | ALTER PROCEDURE <procedure_name>
[ ( @parameter1 <data_type> [ = default ] [ OUT | OUTPUT ],
@parameter2 <data_type> [ = default ] [ OUT | OUTPUT ],
…………………….
@parametern <data_type> [ = default ] [ OUT | OUTPUT ] ) ]
[ WITH <procedure_options> ]
AS
BEGIN
<statements>
END
ALTER:
Modifies a previously created procedure that was created by executing the CREATE PROCEDURE statement. ALTER PROCEDURE does not change permissions and does not affect any dependent stored procedures or triggers.
Procedure Options:
The Procedure provide to options that can be used while creating the procedures. They are:
- Encryption
- Recompile
RECOMPILE:
Indicates that the Database Engine does not cache a plan for this procedure and the procedure is compiled at run time. To instruct the Database Engine to discard plans for individual queries inside a stored procedure, use the RECOMPILE query hint. Use the RECOMPILE query hint when atypical or temporary values are used in only a subset of queries that belong to the stored procedure.
Important: Because the SQL Server 2005 query optimizer typically selects the best execution plan for a query, we recommend that hints, including <query_hint>, be used only as a last resort by experienced developers and database administrators.
ENCRYPTION:
Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users that have no access to system tables or database files cannot retrieve the obfuscated text.
-Procedure contains 2 parts in it: 1. Header 2. Body
-Header part is the content above the AS keyword.
-Body part is the content below the AS keyword.
Passing Parameters to Procedures:
As if we are passing parameters to functions in languages, we can also pass parameters to Procedures. They are the means to pass a value to the procedure or returns from a procedure.
Parameter Modes:
These will specify whether ther parameter is passed into the procedure or returned out of the procedure. SQL Server supports to Parameter Modes:
- IN MODE (DEFAULT)
- OUT OR OUTPUT MODE
IN MODE:
Passes a value into the procedure for execution, this is best suitable for constants & expressions. The value of it can be changed with in the program but cannot be returned. It is the default mode if nothing is specified
OUT MODE:
Passes a value back from the program after the execution of the procedure.
The value of this option can be returned to the calling EXECUTE statement. Use OUTPUT parameters to return values to the caller of the procedure. text, ntext, and image parameters cannot be used as OUTPUT parameters
Syntax for executing the Procedure:
EXEC | EXECUTE [ [@parameter=] <value> [OUTPUT] [DEFAULT] [,…n] ]
A Simple Procedure:
CREATE PROCEDURE PROC1
AS
BEGIN
PRINT 'MY FIRST PROCEDURE'
END
-Executing the above procedure:
EXEC PROC1 OR EXECUTE PROC1
A Procedure which accepts arguments:
ALTER PROCEDURE PROC2(@X INT, @Y INT)
AS
BEGIN
DECLARE @Z INT
SET @Z=@X+@Y
PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z AS VARCHAR)
END
-Executing the above procedure in 2 ways:
- EXEC PROC2 100, 50
- EXEC PROC2 @X=100, @Y=50
A Procedure with Default Values:
CREATE PROCEDURE PROC3(@X INT = 100, @Y INT)
AS
BEGIN
DECLARE @Z INT
SET @Z=@X+@Y
PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z AS VARCHAR)
END
-Executing the above procedure:
- EXEC PROC3 200, 25
- EXEC PROC3 @X=200, @Y=25
- EXEC PROC3 @X=DEFAULT, @Y=25
- EXEC PROC3 @Y=25
-In the 3rd and 4th case it uses the default value of 100 to the varibale X which has been given while creating the procedure.
A Procedure with OUTPUT Parameter:
CREATE PROCEDURE PROC4(@X INT, @Y INT, @Z INT OUTPUT)
AS
BEGIN
SET @Z=@X+@Y
END
- Executing the above procedure:
DECLARE @A INT
EXECUTE PROC4 500, 250, @A OUTPUT
PRINT @A
-A Procedure for Inserting values into the Emp Table:
CREATE PROCEDURE Insert_Emp(@Empno int, @Ename varchar(50), @Sal money, @Deptno int)
As
Begin
INSERT INTO Emp (Empno, Ename, Sal, Deptno) VALUES (@Empno, @Ename, @Sal, @deptno)
End
- Executing the above Procedure:
EXEC Insert_Emp 1016, 'Sudhakar', 2500, 10
-A Procedure for Inserting values into the Emp Table but with Validations:
-This is same as the previous one but with the following validations present in it:
-Empno cannot be NULL value.
-Empno cannot be duplicated.
-Salary cannot be less than 2500.
-Deptno should be present in the Dept Table.
CREATE PROCEDURE Insert_Emp(@Empno int, @Ename varchar(50), @Sal money, @Deptno int)
As
Begin
IF @Empno IS NULL
Begin
Print 'Empno cannot be NULL'
Return
End
IF Exists(SELECT * FROM Emp WHERE Empno=@Empno)
Begin
Print 'Empno cannot be Duplicated'
Return
End
IF @Sal<2500 Begin
Print 'Salary cannot be less than 2500'
Return
End
IF Not Exists(SELECT * FROM Dept WHERE Deptno=@Deptno)
Begin
Print 'Deptno not found in the Dept Table'
Return
End
INSERT INTO Emp (Empno, Ename, Sal, Deptno) VALUES (@Empno, @Ename, @Sal, @deptno)
End
-A Procedure which takes the Empno and returns the Provident Fund and Professional Tax at 12% and 5% respectively on the Salary.
CREATE PROCEDURE Deductions(@Empno int, @PF money OUTPUT, @PT money OUTPUT)
As
Begin
Declare @Sal Money
SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno
SET @PF=@Sal * 0.12
SET @PT=@Sal * 0.05
End
-Executing the above Procedure:
Declare @VPF money, @VPT money
EXEC Deductions 1005, @VPF OUTPUT, @VPT OUTPUT
Print @VPF
Print @VPT
-A Procedure which takes the Empno and prints the Net Salary of the Employee.
CREATE PROCEDURE Net_Sal(@Empno int)
As
Begin
Declare @VSal money, @NSal money, @VPF money, @VPT money
EXEC Deductions @Empno, @VPF OUTPUT, @VPT OUTPUT
SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno
SET @NSal = @VSal – @VPF – @VPT
Print 'Net Salary of the Employee is: ' + Cast(@NSal as Varchar)
End
-Executing the above Procedure:
EXEC Net_Sal 1005
-A Procedure which will Insert values into the Dept table by generating a unique Deptno.
CREATE PROCEDURE Insert_Dept(@Dname varchar(50), @Loc varchar(50))
As
Begin
Declare @Deptno int
Select @Deptno = ISNULL(MAX(Deptno), 0) + 10 FROM Dept
INSERT INTO Dept Values (@Deptno, @Dname, @Loc)
End
-Executing the above Procedure:
EXEC Insert_Dept 'Research', 'Hyderabad'
-A Procedure which is used from transferring amount from one account to the other within the Bank table:
CREATE PROCEDURE Funds_Transfer(@SrcID int, @DestID int, @Amt money)
As
Begin
UPDATE BANK SET Bal = Bal – @Amt WHERE CUSTID=@SrcID
UPDATE BANK SET Bal = Bal + @Amt WHERE CUSTID=@DestID
End
-Executing the above Procedure:
EXEC Funds_Transfer 101, 102, 500
- In the above case if the SrcID or DestID are not present in the table then it will deduct the amount from the other or add the amount from the other to avoid this we need to use transaction management.
- To manage the transaction first we need to identify which statement is executed and which failed for this we use the function @@ROWCOUNT.
- @@ROWCOUNT returns the number of rows affected by the last statement.
-Managing Transactions in the Procedure:
CREATE PROCEDURE Funds_Transfer(@SrcID int, @DestID int, @Amt money)
As
Begin
Declare @Count1 int, @Count2 int
Begin Transaction
UPDATE BANK SET Bal = Bal – @Amt WHERE CUSTID=@SrcID
Set @Count1=@@ROWCOUNT
UPDATE BANK SET Bal = Bal + @Amt WHERE CUSTID=@DestID
Set @Count2=@@ROWCOUNT]
IF @COUNT1=@COUNT2
Begin
COMMIT
PRINT 'TRANSACTION COMMITED'
End
ELSE
Begin
ROLLBACK
PRINT 'TRANSACTION ROLLED BACK'
End
End
Handling Errors in Procedures:
- In SQL Server when a error occurs, the statement that caused the error is terminated, but the execution of the stored procedure or batch continues.
- When stored procedures and batches are executed within the scope of a TRY block, batch abort errors can be handled by the TRY…CATCH construct.
- Errors in Transact-SQL code can be processed using a TRY…CATCH construct similar to the exception-handling features of the languages.
- A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block.
- When an error condition is detected in a Transact-SQL statement contained in a TRY block, control is passed to a CATCH block where it can be processed.
- After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement.
- If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger.
- Transact-SQL statements in the TRY block following the statement that generates an error will not get executed.
- If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement.
- If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger.
- A TRY block starts with the BEGIN TRY statement and ends with the END TRY statement.
- One or more Transact-SQL statements can be specified between the BEGIN TRY and END TRY statements.
- A CATCH block must follow a TRY block immediately.
- A CATCH block starts with the BEGIN CATCH statement and ends with the END CATCH statement.
- In Transact-SQL, each TRY block is associated with only one CATCH block.
-A Procedure which can cause Error:
CREATE PROCEDURE Div(@X int, @Y int)
As
Begin
Declare @Z int
SET @Z=0
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
END
-Executing the above procedure:
EXEC DIVX 100, 20
EXEC DIVX 100, 0
-The first execution will print the result of 5 but the second time execution will raise an error because we cannot divide a number by zero, in this case still it will try to print the result as 0, because even if the error is encountered it will not stop the execution of the program, if we want to stop the execution of the program when an error raises the code has to be written in the following way:
CREATE PROCEDURE Div(@X int, @Y int)
As
Begin
Begin Try
Declare @Z INT
SET @Z=0
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
End Try
Begin Catch
Print Error_Message()
End Catch
End
-Executing the above procedure:
EXEC DIVX 100, 20
EXEC DIVX 100, 0
- Every error has 4 properties to it, they are:
- Msg id
- Msg str
- Severity
- State
For Example try the following statement:
Print 100/0
-This will display the following error message:
Msg 8134, Level 16, State 1,
Divide by zero error encountered.
-In this the Msg id is 8134, Msg str is "Divide by zero error encountered", Severity Level is 16 and State is 1.
Msg id: ID of the message, which is unique across server. Message IDs less than 50000 are system messages.
Msg str: Error message that has to be displayed when the error raises.
Severity Level: Severity level that is associated with the error. Severity levels can range between 0 and 25. Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.
State: Is an arbitrary integer from 1 through 127. If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.
Raising Errors Manually: We can also raise errors manually at some required situations. It is used to return messages back to applications using the same format as a system error or warning message generated by the SQL Server Database Engine. For raising an error manually we use the Raiserror Statement.
It generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.
RAISERROR can return either:
- A user-defined error message that has been created using the sp_addmessage system stored procedure.
- A message string specified in the RAISERROR statement.
RAISERROR can also:
- Assign a specific error number, severity, and state.
- Request that the error be logged in the Database Engine error log and the Microsoft Windows application log.
- Substitute argument values into the message text, much like the C language printf function.
Syntax: RAISERROR ( msg_id | msg_str | @local_variable, severity, state
[, argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
msg_id: Is a user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.
Procedures:
- A stored procedure is a saved collection of Transact-SQL statements or a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters.
- Procedures can be created for permanent use or for temporary use within a session, local temporary procedure, or for temporary use within all sessions, global temporary procedure.
- Stored procedures can also be created to run automatically when an instance of SQL Server starts.
Syntax:
CREATE | ALTER PROCEDURE <procedure_name>
[ ( @parameter1 <data_type> [ = default ] [ OUT | OUTPUT ],
@parameter2 <data_type> [ = default ] [ OUT | OUTPUT ],
…………………….
@parametern <data_type> [ = default ] [ OUT | OUTPUT ] ) ]
[ WITH <procedure_options> ]
AS
BEGIN
<statements>
END
ALTER: Modifies a previously created procedure that was created by executing the CREATE PROCEDURE statement. ALTER PROCEDURE does not change permissions and does not affect any dependent stored procedures or triggers.
Procedure Options: The Procedure provide to options that can be used while creating the procedures. They are:
- Encryption
- Recompile
RECOMPILE: Indicates that the Database Engine does not cache a plan for this procedure and the procedure is compiled at run time. To instruct the Database Engine to discard plans for individual queries inside a stored procedure, use the RECOMPILE query hint. Use the RECOMPILE query hint when atypical or temporary values are used in only a subset of queries that belong to the stored procedure.
Important: Because the SQL Server 2005 query optimizer typically selects the best execution plan for a query, we recommend that hints, including <query_hint>, be used only as a last resort by experienced developers and database administrators.
ENCRYPTION: Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users that have no access to system tables or database files cannot retrieve the obfuscated text.
-Procedure contains 2 parts in it: 1. Header 2. Body
-Header part is the content above the AS keyword.
-Body part is the content below the AS keyword.
Passing Parameters to Procedures: As if we are passing parameters to functions in languages, we can also pass parameters to Procedures. They are the means to pass a value to the procedure or returns from a procedure.
Parameter Modes: These will specify whether ther parameter is passed into the procedure or returned out of the procedure. SQL Server supports to Parameter Modes:
- IN MODE (DEFAULT)
- OUT OR OUTPUT MODE
IN MODE: Passes a value into the procedure for execution, this is best suitable for constants & expressions. The value of it can be changed with in the program but cannot be returned. It is the default mode if nothing is specified
OUT MODE: Passes a value back from the program after the execution of the procedure.
The value of this option can be returned to the calling EXECUTE statement. Use OUTPUT parameters to return values to the caller of the procedure. text, ntext, and image parameters cannot be used as OUTPUT parameters
Syntax for executing the Procedure:
EXEC | EXECUTE [ [@parameter=] <value> [OUTPUT] [DEFAULT] [,…n] ]
A Simple Procedure:
CREATE PROCEDURE PROC1
AS
BEGIN
PRINT 'MY FIRST PROCEDURE'
END
-Executing the above procedure:
EXEC PROC1 OR EXECUTE PROC1
A Procedure which accepts arguments:
ALTER PROCEDURE PROC2(@X INT, @Y INT)
AS
BEGIN
DECLARE @Z INT
SET @Z=@X+@Y
PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z AS VARCHAR)
END
-Executing the above procedure in 2 ways:
- EXEC PROC2 100, 50
- EXEC PROC2 @X=100, @Y=50
A Procedure with Default Values:
CREATE PROCEDURE PROC3(@X INT = 100, @Y INT)
AS
BEGIN
DECLARE @Z INT
SET @Z=@X+@Y
PRINT 'The SUM of the 2 Numbers is: ' + CAST(@Z AS VARCHAR)
END
-Executing the above procedure:
- EXEC PROC3 200, 25
- EXEC PROC3 @X=200, @Y=25
- EXEC PROC3 @X=DEFAULT, @Y=25
- EXEC PROC3 @Y=25
-In the 3rd and 4th case it uses the default value of 100 to the varibale X which has been given while creating the procedure.
A Procedure with OUTPUT Parameter:
CREATE PROCEDURE PROC4(@X INT, @Y INT, @Z INT OUTPUT)
AS
BEGIN
SET @Z=@X+@Y
END
- Executing the above procedure:
DECLARE @A INT
EXECUTE PROC4 500, 250, @A OUTPUT
PRINT @A
-A Procedure for Inserting values into the Emp Table:
CREATE PROCEDURE Insert_Emp(@Empno int, @Ename varchar(50), @Sal money, @Deptno int)
As
Begin
INSERT INTO Emp (Empno, Ename, Sal, Deptno) VALUES (@Empno, @Ename, @Sal, @deptno)
End
- Executing the above Procedure:
EXEC Insert_Emp 1016, 'Sudhakar', 2500, 10
-A Procedure for Inserting values into the Emp Table but with Validations:
-This is same as the previous one but with the following validations present in it:
-Empno cannot be NULL value.
-Empno cannot be duplicated.
-Salary cannot be less than 2500.
-Deptno should be present in the Dept Table.
CREATE PROCEDURE Insert_Emp(@Empno int, @Ename varchar(50), @Sal money, @Deptno int)
As
Begin
IF @Empno IS NULL
Begin
Print 'Empno cannot be NULL'
Return
End
IF Exists(SELECT * FROM Emp WHERE Empno=@Empno)
Begin
Print 'Empno cannot be Duplicated'
Return
End
IF @Sal<2500 Begin
Print 'Salary cannot be less than 2500'
Return
End
IF Not Exists(SELECT * FROM Dept WHERE Deptno=@Deptno)
Begin
Print 'Deptno not found in the Dept Table'
Return
End
INSERT INTO Emp (Empno, Ename, Sal, Deptno) VALUES (@Empno, @Ename, @Sal, @deptno)
End
-A Procedure which takes the Empno and returns the Provident Fund and Professional Tax at 12% and 5% respectively on the Salary.
CREATE PROCEDURE Deductions(@Empno int, @PF money OUTPUT, @PT money OUTPUT)
As
Begin
Declare @Sal Money
SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno
SET @PF=@Sal * 0.12
SET @PT=@Sal * 0.05
End
-Executing the above Procedure:
Declare @VPF money, @VPT money
EXEC Deductions 1005, @VPF OUTPUT, @VPT OUTPUT
Print @VPF
Print @VPT
-A Procedure which takes the Empno and prints the Net Salary of the Employee.
CREATE PROCEDURE Net_Sal(@Empno int)
As
Begin
Declare @VSal money, @NSal money, @VPF money, @VPT money
EXEC Deductions @Empno, @VPF OUTPUT, @VPT OUTPUT
SELECT @Sal=Sal FROM Emp WHERE Empno=@Empno
SET @NSal = @VSal – @VPF – @VPT
Print 'Net Salary of the Employee is: ' + Cast(@NSal as Varchar)
End
-Executing the above Procedure:
EXEC Net_Sal 1005
-A Procedure which will Insert values into the Dept table by generating a unique Deptno.
CREATE PROCEDURE Insert_Dept(@Dname varchar(50), @Loc varchar(50))
As
Begin
Declare @Deptno int
Select @Deptno = ISNULL(MAX(Deptno), 0) + 10 FROM Dept
INSERT INTO Dept Values (@Deptno, @Dname, @Loc)
End
-Executing the above Procedure:
EXEC Insert_Dept 'Research', 'Hyderabad'
-A Procedure which is used from transferring amount from one account to the other within the Bank table:
CREATE PROCEDURE Funds_Transfer(@SrcID int, @DestID int, @Amt money)
As
Begin
UPDATE BANK SET Bal = Bal – @Amt WHERE CUSTID=@SrcID
UPDATE BANK SET Bal = Bal + @Amt WHERE CUSTID=@DestID
End
-Executing the above Procedure:
EXEC Funds_Transfer 101, 102, 500
- In the above case if the SrcID or DestID are not present in the table then it will deduct the amount from the other or add the amount from the other to avoid this we need to use transaction management.
- To manage the transaction first we need to identify which statement is executed and which failed for this we use the function @@ROWCOUNT.
- @@ROWCOUNT returns the number of rows affected by the last statement.
-Managing Transactions in the Procedure:
CREATE PROCEDURE Funds_Transfer(@SrcID int, @DestID int, @Amt money)
As
Begin
Declare @Count1 int, @Count2 int
Begin Transaction
UPDATE BANK SET Bal = Bal – @Amt WHERE CUSTID=@SrcID
Set @Count1=@@ROWCOUNT
UPDATE BANK SET Bal = Bal + @Amt WHERE CUSTID=@DestID
Set @Count2=@@ROWCOUNT]
IF @COUNT1=@COUNT2
Begin
COMMIT
PRINT 'TRANSACTION COMMITED'
End
ELSE
Begin
ROLLBACK
PRINT 'TRANSACTION ROLLED BACK'
End
End
Handling Errors in Procedures:
- In SQL Server when a error occurs, the statement that caused the error is terminated, but the execution of the stored procedure or batch continues.
- When stored procedures and batches are executed within the scope of a TRY block, batch abort errors can be handled by the TRY…CATCH construct.
- Errors in Transact-SQL code can be processed using a TRY…CATCH construct similar to the exception-handling features of the languages.
- A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block.
- When an error condition is detected in a Transact-SQL statement contained in a TRY block, control is passed to a CATCH block where it can be processed.
- After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement.
- If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger.
- Transact-SQL statements in the TRY block following the statement that generates an error will not get executed.
- If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement.
- If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger.
- A TRY block starts with the BEGIN TRY statement and ends with the END TRY statement.
- One or more Transact-SQL statements can be specified between the BEGIN TRY and END TRY statements.
- A CATCH block must follow a TRY block immediately.
- A CATCH block starts with the BEGIN CATCH statement and ends with the END CATCH statement.
- In Transact-SQL, each TRY block is associated with only one CATCH block.
-A Procedure which can cause Error:
CREATE PROCEDURE Div(@X int, @Y int)
As
Begin
Declare @Z int
SET @Z=0
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
END
-Executing the above procedure:
EXEC DIVX 100, 20
EXEC DIVX 100, 0
-The first execution will print the result of 5 but the second time execution will raise an error because we cannot divide a number by zero, in this case still it will try to print the result as 0, because even if the error is encountered it will not stop the execution of the program, if we want to stop the execution of the program when an error raises the code has to be written in the following way:
CREATE PROCEDURE Div(@X int, @Y int)
As
Begin
Begin Try
Declare @Z INT
SET @Z=0
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
End Try
Begin Catch
Print Error_Message()
End Catch
End
-Executing the above procedure:
EXEC DIVX 100, 20
EXEC DIVX 100, 0
- Every error has 4 properties to it, they are:
- Msg id
- Msg str
- Severity
- State
For Example try the following statement:
Print 100/0
-This will display the following error message:
Msg 8134, Level 16, State 1,
Divide by zero error encountered.
-In this the Msg id is 8134, Msg str is "Divide by zero error encountered", Severity Level is 16 and State is 1.
Msg id: ID of the message, which is unique across server. Message IDs less than 50000 are system messages.
Msg str: Error message that has to be displayed when the error raises.
Severity Level: Severity level that is associated with the error. Severity levels can range between 0 and 25. Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.
State: Is an arbitrary integer from 1 through 127. If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.
Raising Errors Manually: We can also raise errors manually at some required situations. It is used to return messages back to applications using the same format as a system error or warning message generated by the SQL Server Database Engine. For raising an error manually we use the Raiserror Statement.
It generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.
RAISERROR can return either:
- A user-defined error message that has been created using the sp_addmessage system stored procedure.
- A message string specified in the RAISERROR statement.
RAISERROR can also:
- Assign a specific error number, severity, and state.
- Request that the error be logged in the Database Engine error log and the Microsoft Windows application log.
- Substitute argument values into the message text, much like the C language printf function.
Syntax: RAISERROR ( msg_id | msg_str | @local_variable, severity, state
[, argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
msg_id: Is a user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.
msg_str: Is a user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. When msg_str is specified, RAISERROR raises an error message with an error number of 50000.
msg_str is a string of characters with optional embedded conversion specifications. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. The parameters that can be used in msg_str are:
d or i Signed Integer
s String
u Unsigned Integer
These type specifications are based on the ones originally defined for the printf function in the C standard library. The type specifications used in RAISERROR message strings map to Transact-SQL data types, while the specifications used in printf map to C language data types.
@local_variable: Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar.
Severity: Is the user-defined severity level associated with this message. When using msg_id to raise a user-defined message created using sp_addmessage, the severity specified on RAISERROR overrides the severity specified in sp_addmessage.
Any user can specify severity levels from 0 through 18. Members of the sysadmin fixed server role permissions can only specify severity levels from 19 through 25, for which the WITH LOG option is required.
State: Is an arbitrary integer from 1 through 127. A negative value for state defaults to 1. The value 0 or values larger than 127 generate an error.
Argument: Are the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. No other data types are supported.
Option: Is a custom option for the error and can be one of the values in the following table.
- LOG: Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role can specify WITH LOG.
- NOWAIT: Sends messages immediately to the client.
- SETERROR: Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.
The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block.
The error is returned to the caller if RAISERROR is run:
- Outside the scope of any TRY block.
- With a severity of 10 or lower in a TRY block.
- With a severity of 20 or higher that terminates the database connection.
CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block by using system functions such as ERROR_NUMBER and ERROR_MESSAGE to retrieve the original error information. @@ERROR is set to 0 by default for messages with a severity from 1 through 10.
-A procedure to divide 2 numbers and will raise an error when the divisor is 1.
CREATE PROCEDURE Divx(@X int, @Y int)
As
Begin
Declare @Z INT
SET @Z=0
IF @Y=1
RAISERROR ('CANNOT DIVIDE BY 1′, 15, 1)
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
End
-Executing the above procedure:
EXEC DIVX 100, 20
EXEC DIVX 100, 1
-In the above case the RAISERROR statement raises the error but still next statements get executed. So if we want to stop the execution on the same line the code has to be enclosed with in the Try and Catch blocks.
CREATE PROCEDURE Divx(@X int, @Y int)
As
Begin
Begin Try
Declare @Z INT
SET @Z=0
IF @Y=1
RAISERROR ('CANNOT DIVIDE BY 1′, 15, 1)
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
End Try
Begin Catch
PRINT ERROR_MESSAGE()
End Catch
End
-Executing the above procedure:
EXEC DIVX 100, 1
-In the above case when the error is raised the control transfers to the catch block and prints the error message associated with the error.
-If we want to customize the error message with formatting we can use the Raiserror statement as following:
RAISERROR ('CANNOT DIVIDE %d WITH %d', 15, 1, @X, @Y)
-In this case substituting the value of variable @X at the first % d location and the @y at second % d location it will generate the error message.
-We can also use the "WITH LOG" option at the end of the string to write the error message into the SQL Server Log File as following:
RAISERROR ('CANNOT DIVIDE %d WITH %d', 15, 1, @X, @Y) WITH LOG
-After running the procedure which will generate the error go and verify under the following location in the Object Explorer of the Management Studio i.e. under the Management node, SQL Server logs node, Current node click on it where we find the error message.
Pre-defined Errors: All the predefined error list of sql server can be found in the SYS.Messages Catalog View. Query on the database with the following statement where we can view the list of predefined errors:
-SELECT * FROM SYS.MESSAGES
-This will display the list of errors with their error_id, severity level, error_msg and language_id.
-We can also insert our own user defined error messages into it and use them when required, but because this is a System Catalog View we cannot directly insert records into it, so SQL Server provides a predefined Procedure SP_AddMessage which when called will insert the record into the Catalog View.
SP_AddMessage: Stores a new user-defined error message in an instance of the SQL Server Database Engine. Messages stored using sp_addmessage can be viewed using the sys.messages catalog view.
Syntax: sp_addmessage [ @msgnum = ] msg_id ,
[ @severity = ] severity ,
[ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] 'with_log' ]
[ , [ @replace = ] 'replace' ]
[ @msgnum = ] msg_id: Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647. The combination of msg_id and language must be unique; an error is returned if the ID already exists for the specified language.
[ @severity = ] severity: Is the severity level of the error. severity is smallint with a default of NULL. Valid levels are from 1 through 25.
[ @msgtext = ] 'msg': Is the text of the error message. msg is nvarchar(255) with a default of NULL.
[ @lang = ] 'language': Is the language for this message. Because multiple languages can be installed on the same server, language specifies the language in which each message is written. When language is omitted, the language is the default language for the session.
[ @with_log = ] { 'TRUE' | 'FALSE' ] }: Is whether the message is to be written to the Windows application log when it occurs. The @with_log is varchar(5)with a default of FALSE. If TRUE, the error will be written in to the Windows application log. If a message is written to the Windows application log, it is also written to the Database Engine error log file.
[ @replace = ] 'replace': If specified as the string replace, an existing error message is overwritten with new message text and severity level. @replace is varchar(7) with a default of NULL. This option must be specified if msg_id already exists. If you replace a U.S. English message, the severity level is replaced for all messages in all other languages that have the same msg_id.
EXEC sp_addmessage 50001, 16, 'Cannot Divide the Number by One'
-The above statement will insert a record into the SYS.Messages System Catalog after it was inserted we can use the raiseerror statement as following in our previous procedure:
Raiserror(50001, 16, 1)
-So when the error is raised the corresponding error message is picked out from the Catalog View and displayed to the user.
-Add Procedure, which will delete a record from the dept table for the given deptno and will raise an error if the deptno has any child records in the emp table.
CREATE PROCEDURE Delete_Dept(@Deptno int)
As
Begin
IF EXISTS(SELECT * FROM Emp WHERE Deptno=@Deptno)
Raiserror('Child Records Found', 15, 1)
ELSE
DELETE FROM Dept WHERE Deptno=@Deptno
End
-After creating a Procedure at any time if we want to view the content of it write the following statement:
SP_HELPTEXT <procedure_name>
SP_HELPTEXT Delete_Dept
Creating a Procedure using With Encryption Option:
CREATE PROCEDURE Delete_Dept(@Deptno int)
WITH ENCRYPTION
As
Begin
IF EXISTS(SELECT * FROM Emp WHERE Deptno=@Deptno)
Raiserror('Child Records Found', 15, 1)
ELSE
DELETE FROM Dept WHERE Deptno=@Deptno
End
-If the Procedure is created by using the With Encryption Option even if we use the SP_HELPTEXT also we cannot view the content of it. Is a user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. When msg_str is specified, RAISERROR raises an error message with an error number of 50000.
msg_str is a string of characters with optional embedded conversion specifications. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. The parameters that can be used in msg_str are:
d or i Signed Integer
s String
u Unsigned Integer
These type specifications are based on the ones originally defined for the printf function in the C standard library. The type specifications used in RAISERROR message strings map to Transact-SQL data types, while the specifications used in printf map to C language data types.
@local_variable: Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar.
Severity: Is the user-defined severity level associated with this message. When using msg_id to raise a user-defined message created using sp_addmessage, the severity specified on RAISERROR overrides the severity specified in sp_addmessage.
Any user can specify severity levels from 0 through 18. Members of the sysadmin fixed server role permissions can only specify severity levels from 19 through 25, for which the WITH LOG option is required.
State: Is an arbitrary integer from 1 through 127. A negative value for state defaults to 1. The value 0 or values larger than 127 generate an error.
Argument: Are the parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. No other data types are supported.
Option: Is a custom option for the error and can be one of the values in the following table.
- LOG: Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role can specify WITH LOG.
- NOWAIT: Sends messages immediately to the client.
- SETERROR: Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.
The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block.
The error is returned to the caller if RAISERROR is run:
- Outside the scope of any TRY block.
- With a severity of 10 or lower in a TRY block.
- With a severity of 20 or higher that terminates the database connection.
CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block by using system functions such as ERROR_NUMBER and ERROR_MESSAGE to retrieve the original error information. @@ERROR is set to 0 by default for messages with a severity from 1 through 10.
-A procedure to divide 2 numbers and will raise an error when the divisor is 1.
CREATE PROCEDURE Divx(@X int, @Y int)
As
Begin
Declare @Z INT
SET @Z=0
IF @Y=1
RAISERROR ('CANNOT DIVIDE BY 1′, 15, 1)
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
End
-Executing the above procedure:
EXEC DIVX 100, 20
EXEC DIVX 100, 1
-In the above case the RAISERROR statement raises the error but still next statements get executed. So if we want to stop the execution on the same line the code has to be enclosed with in the Try and Catch blocks.
CREATE PROCEDURE Divx(@X int, @Y int)
As
Begin
Begin Try
Declare @Z INT
SET @Z=0
IF @Y=1
RAISERROR ('CANNOT DIVIDE BY 1′, 15, 1)
SET @Z=@X/@Y
PRINT 'The Output is: ' + Cast(@Z as varchar)
End Try
Begin Catch
PRINT ERROR_MESSAGE()
End Catch
End
-Executing the above procedure:
EXEC DIVX 100, 1
-In the above case when the error is raised the control transfers to the catch block and prints the error message associated with the error.
-If we want to customize the error message with formatting we can use the Raiserror statement as following:
RAISERROR ('CANNOT DIVIDE %d WITH %d', 15, 1, @X, @Y)
-In this case substituting the value of variable @X at the first % d location and the @y at second % d location it will generate the error message.
-We can also use the "WITH LOG" option at the end of the string to write the error message into the SQL Server Log File as following:
RAISERROR ('CANNOT DIVIDE %d WITH %d', 15, 1, @X, @Y) WITH LOG
-After running the procedure which will generate the error go and verify under the following location in the Object Explorer of the Management Studio i.e. under the Management node, SQL Server logs node, Current node click on it where we find the error message.
Pre-defined Errors: All the predefined error list of sql server can be found in the SYS.Messages Catalog View. Query on the database with the following statement where we can view the list of predefined errors:
-SELECT * FROM SYS.MESSAGES
-This will display the list of errors with their error_id, severity level, error_msg and language_id.
-We can also insert our own user defined error messages into it and use them when required, but because this is a System Catalog View we cannot directly insert records into it, so SQL Server provides a predefined Procedure SP_AddMessage which when called will insert the record into the Catalog View.
SP_AddMessage: Stores a new user-defined error message in an instance of the SQL Server Database Engine. Messages stored using sp_addmessage can be viewed using the sys.messages catalog view.
Syntax: sp_addmessage [ @msgnum = ] msg_id ,
[ @severity = ] severity ,
[ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] 'with_log' ]
[ , [ @replace = ] 'replace' ]
[ @msgnum = ] msg_id: Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647. The combination of msg_id and language must be unique; an error is returned if the ID already exists for the specified language.
[ @severity = ] severity: Is the severity level of the error. severity is smallint with a default of NULL. Valid levels are from 1 through 25.
[ @msgtext = ] 'msg': Is the text of the error message. msg is nvarchar(255) with a default of NULL.
[ @lang = ] 'language': Is the language for this message. Because multiple languages can be installed on the same server, language specifies the language in which each message is written. When language is omitted, the language is the default language for the session.
[ @with_log = ] { 'TRUE' | 'FALSE' ] }: Is whether the message is to be written to the Windows application log when it occurs. The @with_log is varchar(5)with a default of FALSE. If TRUE, the error will be written in to the Windows application log. If a message is written to the Windows application log, it is also written to the Database Engine error log file.
[ @replace = ] 'replace': If specified as the string replace, an existing error message is overwritten with new message text and severity level. @replace is varchar(7) with a default of NULL. This option must be specified if msg_id already exists. If you replace a U.S. English message, the severity level is replaced for all messages in all other languages that have the same msg_id.
EXEC sp_addmessage 50001, 16, 'Cannot Divide the Number by One'
-The above statement will insert a record into the SYS.Messages System Catalog after it was inserted we can use the raiseerror statement as following in our previous procedure:
Raiserror(50001, 16, 1)
-So when the error is raised the corresponding error message is picked out from the Catalog View and displayed to the user.
-Add Procedure, which will delete a record from the dept table for the given deptno and will raise an error if the deptno has any child records in the emp table.
CREATE PROCEDURE Delete_Dept(@Deptno int)
As
Begin
IF EXISTS(SELECT * FROM Emp WHERE Deptno=@Deptno)
Raiserror('Child Records Found', 15, 1)
ELSE
DELETE FROM Dept WHERE Deptno=@Deptno
End
-After creating a Procedure at any time if we want to view the content of it write the following statement:
SP_HELPTEXT <procedure_name>
SP_HELPTEXT Delete_Dept
Creating a Procedure using With Encryption Option:
CREATE PROCEDURE Delete_Dept(@Deptno int)
WITH ENCRYPTION
As
Begin
IF EXISTS(SELECT * FROM Emp WHERE Deptno=@Deptno)
Raiserror('Child Records Found', 15, 1)
ELSE
DELETE FROM Dept WHERE Deptno=@Deptno
End
-If the Procedure is created by using the With Encryption Option even if we use the SP_HELPTEXT also we cannot view the content of it.
No comments:
Post a Comment