Exception Handling in SQL

In this blog, we’ll discuss about Exception Handling in Microsoft SQL Server.

Exception handling can be done by using TRY CATCH block in SQL Server and put all the T-SQL statements inside it. Only 1 CATCH block can be associate with TRY block.

Note :- In case of User Defined Function or UDF in SQL it is not easy to implement the TRY CATCH block due the limitation of UDF. UDF can be either Scalar type of table value. In case of Scalar it is possible to use it for few cases but in case of table valued it is not possible.

Syntax of Exception Handling

Begin Try
      SQL Statement 1
      SQL Statement 2
      ..
      ..
End Try
Begin Catch
      ERROR Message Print
End Catch

To retrieve the error message and print in Catch block, we can use the below functions.

  1. ERROR_NUMBER() – This returns the error number .
  2. ERROR_LINE() – This returns the line number of T-SQL statement that caused error.
  3. ERROR_SEVERITY() – This returns the severity level of the error.
  4. ERROR_STATE() – This returns the state number of the error.
  5. ERROR_PROCEDURE() – This returns the name of the stored procedure or trigger where the error occurred.
  6. ERROR_MESSAGE() – This returns the full text of error message. The text includes the values supplied for any parameters, such as lengths, object names, or times.

Example

BEGIN TRY
       DECLARE @num INT
       — Divide by zero to generate Error
       SET @num = 15/0
END TRY
BEGIN CATCH
     SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,                      ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure,                        ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage
END CATCH

Note :- In SQL Server 2012, to handle all the error related information we can use only THROW statement instead of multiple functions. Also it is more accurate to get the error description whether it is single or multiple errors.

Example

BEGIN TRY
      DECLARE @num INT
      — Divide by zero to generate Error
      SET @num = 15/0
END TRY
BEGIN CATCH
       THROW
END CATCH

Exception Handling in Stored Procedure

Create Procedure Test
as
Begin
    Begin Try
         Select * from Table1
    End Try
    Begin Catch
         Throw
     End Catch
End

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Advertisements

Stored Procedure

In this blog, we’ll discuss about Stored Procedures in Microsoft SQL Server.

A Stored Procedure is a set of statements with an assigned name that’s stored in the database in compiled form so that it can be shared by a number of programs.

Execution process of Stored Procedure

  1. PARSED – In this step, all the statements written inside the stored procedure are first parsed to check the syntax errors.
  2. ALGEBRIZED – In this step, all the objects defined inside the stored procedure are changed into a consistent format. Example – A table might be referenced as ABC or dbo.ABC or Database_Name.dbo.ABC. All these will change internally to a same consistent format.But NO changes will be made physically in the procedure itself, changes are internal.
  3. OPTIMIZED – In this step, the SQL Server engine looks at a query and determines the optimal strategy for execution.  It looks at what the query is doing, uses the parameter values to look at the statistics, does some calculations and eventually decides on what steps are required to resolve the query.

Once the above 3 steps are done successfully, SQL Server will save a copy of the compiled execution plan in procedure cache. Every subsequent call to the same store procedure with the same parameters (if any) will use that same execution plan and save the above 3 steps of execution process by default.It finally leads to improve the performance.

Note :- We can see, store multiple versions and even compare the execution plans of any stored procedures in SQL SERVER 2016 version.

Advantages of Stored Procedure

  1. It helps to simplified the maintenance & administration process in database.
  2. It can perform single or multiple task in database.
  3. It can accept zero or single or multiple parameters from the user as per its design.
  4. It can return no output or single or multiple outputs.
  5. It helps to improve the security by providing the different level of access rights to different users.
  6. It helps to improve the performance by maintaining the execution plan in procedure cache by default which automatically use next time for the execution of the procedure.
  7. It helps to create the code re-usability concept by using the same procedure in multiple location with different set of parameter value.
  8. It avoids the SQL Injection attack and safe guard the most important part of any business,.i.e., database.
  9. It can be nested.Multiple Stored procedure can be defined inside another stored procedure. MAX level of stored procedure nesting can be done up to 10 levels
  10. A complete table can be passed to a Stored Procedure as a parameter by using Table-Valued parameter.To know more about it please visit the Table-Value parameter blog.

Disadvantage of Stored Procedure

  1. Stored procedures with never give error during creation about the existence of any object defined inside it.Like it’ll never check whether the table or view or function exists or not.It’ll only check during run time.
  2. It’ll never checks the logic and can produce run time error.
  3. Creating optimize stored procedure is sometimes difficult while dealing with multiple tables with a very large database.
  4. User must have proper rights to create,modify,delete and execute the stored procedures in a database.
  5. Stored procedures with input parameters cause the problem of Parameter Sniffing.

Types of Stored Procedure

  1. System Defined Stored Procedure – This is build-in stored procedures.We cannot modified it.These are physically stored in hidden Sql Server Resource Database and logically appear in the sys schema of each user defined and system defined database. These procedure starts with the sp_ prefix.
  2. Extended Stored Procedure – An extended stored procedure (xp) is a dynamic link library that runs directly in the address space of SQL Server and is programmed using the SQL Server Open Data Services API.As per Microsoft MSDN, this feature will be removed in a future version of Microsoft SQL Server.
  3. User Defined Stored Procedure – All the stored procedures defined by the user in a database are known as User Defined Stored Procedure.
  4. CLR Stored Procedure – This is defined with SQL Server 2005 when Microsoft integrate CLR (Common Language Runtime) in SQL Server. Here user can create a fully functional Stored Procedure in either C# or VB.net and generate a DLL/Assembly file.Next in order to call and use that, we need to attached that DLL file in SQL Server and simply call the procedure.
  5. Temporary Stored Procedure – Its behavior is similar to Temp Tables.While creating the stored procedures if we add the # symbol before Procedure name it’ll make it temporary and stored it inside Temp database.It can be 2 types.
    1. Local Temp Stored Procedure – Local temp procedure can be create by using single hash(#) symbol in front of the procedure name.This procedure is visible and accessible to single instance where it is defined or explicitly deleted. It gets automatically drop when the instance is close. It behaves in a same way as normal procedure and can perform all the actions.
    2. Global Temp Stored Procedure – Global temp procedure can be create by using double hash(##) symbol in front of the procedure name.This procedure is visible and accessible to all the instance. It gets automatically drop when the instance is close where it is defined or explicitly deleted. It behaves in a same way as normal procedure and can perform all the actions.

Returning data from SQL Stored Procedure 

SQL Procedure can be use to perform anything like doing a calculation, calling a function / procedure, performing any DDL,DML,TCL or DCL statement.To return any value other from Stored Procedure we can use below mentioned ways

  1. By using the SELECT statement.
  2. By using the RETURN keyword.
  3. By using the OUTPUT keyword with the parameter.

Difference in RETURN & OUTPUT in SQL Stored Procedure

RETURN OUTPUT
Only Integer type values can be return by using RETURN keyword. Any data type values can be return by using OUTPUT keyword
Multiple RETURN keywords can be used in a procedure.But only a single  RETURN keyword can be use to return a value at a time from the procedure. Multiple OUTPUT keywords can be used in a procedure.All the OUTPUT keyword can be use to return all value at a time from the procedure.
It cannot be use with parameter. It is always in use with parameter.

Execute or Run a Stored Procedure

EXEC <Database_Name>.<Schema_Name>.<Table_Name>.<Stored Procedure Name>

Here Optional parameters are : Database_Name, Schema_Name,Table_Name,Parameters

Note : –We can either use EXEC or EXECUTE or SP_EXECUTE keyword. Here SP_EXECUTE allows the execution of procedure secure against SQL injection.

Example

–Simple Stored Procedure without any parameter
Create Procedure Test
as
Begin
       Select * from Table1
End

–Execute the procedure
EXEC Test

–Stored Procedure with input parameter
Create Procedure Test
(
     @id int,
      @name varchar(100)
)
as
Begin
      Select * from Table1 where Eid=@id and Ename=@name
End

–Execute the procedure
Declare @Id int,@Name varchar(100)
Set @Id=5
Set @Name=’TT’
EXEC Test @Id,@Name

–Stored Procedure with input and output parameter
Create Procedure Test
(
     @Salary int,
    @Count int Output
)
as
Begin
     Select @count=COUNT(ID) from Table1 where Salary > @Salary
End

–Execute the procedure
Declare @Sal int,@Count int
Set @Sal=5000
EXEC Test @Sal,@Count Output
Select @Count as ‘Total Count’

–Local Temp Stored Procedure
Create Procedure #Test
(
       @Salary int,
       @Count int Output
)
as
Begin
       Select @count=COUNT(ID) from Table1 where Salary > @Salary
End

–Execute the procedure
Declare @Sal int,@Count int
Set @Sal=5000
EXEC #Test @Sal,@Count Output
Select @Count as ‘Total Count’
–Global Temp Stored Procedure
Create Procedure ##Test
(
      @Salary int,
      @Count int Output
)
as
Begin
       Select @count=COUNT(ID) from Table1 where Salary > @Salary
End

–Execute the procedure
Declare @Sal int,@Count int
Set @Sal=5000
EXEC ##Test @Sal,@Count Output
Select @Count as ‘Total Count’

–Stored Procedure with Default Parameter
Create Procedure Test
(
     @Id int NULL
)
as
Begin
         If ISNULL(@Id,”)=”
                Select * from Table1
        Else
                Select * from Table1 where Eid=@Id
End

–Execute the procedure with Parameter – It’ll execute the Else Condition
Declare @Id int
Set @Id=5
EXEC Test @Id

–Execute the procedure without Parameter – It’ll execute the If condition
EXEC Test

–Few example of System stored procedure
sp_rename : It is used to rename a database object like stored procedure,views,table etc.
sp_help : It provides details on any database object.
sp_helpdb : It provide the details of the databases defined in the Sql Server.
sp_helptext : It provides the text of a stored procedure reside in Sql Server.

–CLR stored procedure
Step1 : Create a Library in Visual Studio in C# or VB.Net and save the DLL file, let’s say CLRProc

using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class Myproc
{
     [Microsoft.SqlServer.Server.SqlProcedure]
      public static void InsertData()
     {
         using SqlConnection con = new SqlConnection(“Data Source=Test1;Initial                                                                                 Catalog=ABC;User ID=sa;Password=sqlserver2005”);
        {
             SqlCommand com = new SqlCommand(“Insert into Table1 values (“abc”)”);
             con.Open();
             com.Connection=con;
             com.ExecuteNonQuery();
             con.close();
        }
   }
}

Step2 : Attach the DLL in SQL Server
In the Left Side Expand the Database => Progammability => Assembly => Right Click and Browse to locate the DLL => Select the DLL => OK

Step3 : Call the assembly file
External Name <DLL_FileName>.<Class_Name>.<Function_Name>

Here it’ll be

External Name CLRProc.Myproc.InsertData

Exception handling in Stored Procedure can be done by Try Catch block. To know the details about it please go through the Exceptional handling blog.

Transactions can also be handle in Stored Procedure by Begin Transaction,Commit & Rollback.To know the details about it please go through the Transactions blog.

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Click here for know about Parameter Sniffing in Stored Procedure

 

Parameter Sniffing in Stored Procedure

In this blog, we’ll discuss about problem of Parameter Sniffing in Stored Procedures in Microsoft SQL Server and ways to avoid this.

SQL Server engine use the cache copy of execution plan each execution of the store procedure by default. In case the procedure consists of input parameter it leads to common problem called Parameter Sniffing. Parameter sniffing can lead to inefficient execution plans sometimes; especially when a stored procedure is called with same set of parameter values that have different values. In order to avoid this problem, we can use any of the below mentioned ways

  1. Use local variables
  2. OPTION (RECOMPILE)
  3. OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
  4. OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))

Example

–Parameter Sniffing Solution 1: OPTION (Recompile) : Here the procedure will re-compile the procedure every time it executes
Create Procedure Test
(
    @Id int
)
as
Begin
      Select * from Table1 where Eid=@Id option (Recompile)
End

–Parameter Sniffing Solution 2: OPTIMIZE FOR (@VARIABLE=VALUE) : Let’s assume that this procedure is called %99 percent for “XXX” and only %1 percent for “YYY”.So it is optimize for “XXX”
Create Procedure Test
(
      @Id int
)
as
Begin
       Select * from Table1 where Eid=@Id option (optimize for (@city=‘XXX’))
End

–Parameter Sniffing Solution 3: OPTIMIZE FOR (@VARIABLE UNKNOWN) : Here the called parameter value is not known.So we left it to SQL Server Optimizer to decide the best plan
Create Procedure Test
(
      @Id int
)
as
Begin
         Select * from Table1 where Eid=@Id option (optimize for (@city UNKNOWN))
End

–Parameter Sniffing Solution 4: Use local variable : Here all the parameters are first stored in local variable and then those variables will be use in the procedure
Create Procedure Test
(
       @Id int
)
as
Begin
        Declare @lid as int
        set @lid=@Id
        Select * from Table1 where Eid=@lid
End

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Click here for basics of Stored Procedure