- 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