Stored Procedures in SQL Server
In this article, I am giving a quick overview about Stored Procedure in SQL Server. After completing this article you will understand:
What is mean by Stored Procedure?
How to manage Transactions in Stored Procedure?
Please give your valuable suggestions and feedback to improve this article.
I have already discussed TSQL Programming Basics in SQL Server in my previous post; I will recommend you to go through this post before you start on Stored Procedure.
I hope you have gone through the basics of TSL Programming basics, now we can start.
As you know we have two types of blocks in TSQL programming, Anonymous Blocks and Sub-Program Blocks.
Sub Program blocks are of two types in SQL Server.
1. Procedures
In this article I will be focusing on Procedures, because both of the topics are very vast so I will be writing another article on functions.
What is mean by Stored Procedure?
A Stored Procedure is a saved collection of TSQL statement that you can reuse over and over again.
In addition stored procedures provide input and output parameters, depending on your input parameters stored procedure can return needed result set.Stored Procedure can run independently, it can be executed using EXEC or EXECUTE command. The greatest advantage of stored procedure is we can do Error handling using TRY, CATCH statements in Stored Procedure.
How to Create, Alter, Drop Stored Procedure in SQL Server?
Here we will understand how to create simple stored procedure, We will also create Stored Procedure with more advanced options later in this article.
Before I start with the example, let’s understand the syntax we use to create a simple stored procedure.
Syntax to create stored procedure
CREATE PROCEDURE <Procedure-Name> (
<Procedure-Name>: This is the procedure name you will give to store your query in SQL Server; you have to remember this name because you call the procedure using its name.
Let’s create a simple Stored Procedure.
Stored Procedure can be created either by CREATE PROCEDURE or CREATE PROC, after giving the Stored Procedure name we need to use AS keyword. Just after the AS keyword we write our regular SQL query. If we have multiple statements then we keep it in BLOCK otherwise we can directly write the SQL Statement.
We need some data in our TEACHMESQLSERVER database, let’s populate some data.
If you don’t have TEACHMESQLSERVER database in your SQL Server instance then please use below query to create the database.
CREATE DATABASE TEACHMESQLSERVER
Let's populate some data.
USE TEACHMESQLSERVER
As you can see from the above result set we have successfully populated the data. Suppose SELECT * FROM EMP is the query you write and run again and again. In this example I am using the simplest example to understand the concept but in real world it will be a set of SQL Statements.
Let’s create a stored procedure for this.
CREATE PROCEDURE EMPLIST AS
Congratulations you have successfully created your first stored procedure. You can find list of all stored procedure from sys.Procedures table using the below query
GO
As you can see from the result set of above query, EMPLIST is a SQL Stored Procedure. You can also find the other related information.
You can also find the list of Stored Procedures using object explorer in SQL Server Management Studio.
Follow the below path
As you can see under the Stored Procedures folder, our recently created EMPLIST stored procedure is saved.
Now let’s execute recently created EMPLIST stored procedure. We can execute stored procedure using EXEC or EXECUTE command.
EXEC EMPLIST
As you can see it is returning the list of all employees present in the EMP table.
Stored Procedures with Parameter
We have already created a simple stored procedure without any parameter; we can create Procedures with parameters which mean we can pass parameters to Procedure; they are the means to pass a value to the procedure or returns from a procedure.These modes will specify whether the parameter is passed into the procedure or returned out of the procedure.
There are two modes of parameters in Stored Procedure.
IN MODE- Passes a value into the procedure for execution this is the best suitable for Constants and expressions. The value of the parameter can be changed in the program but it can’t 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 parameter to return values to the caller of the procedure. Text, ntext, Image data type parameters cannot be used as OUTPUT parameters. Stored Procedure can’t return table variable.
Now let’s create little complex stored procedure using input parameters.
1. Input parameters
Problem- How can you get employees details based on EMPID?
You can modify your Stored Procedure using ALTER; you can use ALTER PROCEDURE at place of CREATE PROCEDURE.
Now let’s modify the stored procedure which takes Input parameter for EMPID and returns the result based on the input parameter.We will modify our EMPLIST stored procedure for the above problem.
ALTER PROCEDURE EMPLIST (@EMPID INT)
You have successfully modified the EMPLIST stored procedure. As you can see from the above query your procedure is having one parameter, we have also modified the SQL statement. @EMPID parameter is passed in the SQL Query, based on this parameter value our procedure will return the result set.
Now let’s execute the procedure
GO
Once you ran the above query you got an error message as shown below.
As you know we had modified our stored procedure, which is expecting the input parameter value but we didn’t give it at the time of execution.
There are multiple ways to assign parameter value, now let’s execute the procedure with input parameter value using any of the below method.
GO
OR
EXEC EMPLIST @EMPID=1
Bingo our above procedure ran successfully for both of the above execute statements and returned the expected result set.
Default Parameter values
As you know now when you don’t give your parameter value at the time of execution it ends up with an error message. If you don’t want this to happen then you can set a default value for your input parameter. In below example we will be doing the same.
Let’s modify the EMPLIST stored procedure with input parameter default value.
ALTER PROCEDURE EMPLIST (@EMPID INT =1 )
As you can see from the above SQL Statement this time we have given 1 value for @EMPID input parameter.
Now let’s execute the procedure using any of the below method.
GO
OR
EXEC EMPLIST @EMPID=2
OR
EXEC EMPLIST Default
OR
EXEC EMPLIST @EMPID=Default
Bingo this time our above execute statement ran successfully and returned the expected result set.This time It didn’t give any error; you can change your parameter value as we did in previous exercise.
Multiple Parameters
Setting up multiple parameters is not very difficult; you have to separate your each parameter using comma separator. At the time of execution also you have to use comma separator.
2. Output Parameters
This is another advantage of Stored Procedure. It can take input from parameter and it can return parameter. This can be helpful when you call another stored procedure that does not return any data, but returns parameter values to be used by the calling stored procedure.
Let’s understand how to create procedure with output parameters.
Problem- How to return all records count from EMP table using stored procedure?
ALTER PROCEDURE EMPLIST (@RECORDCOUNT INT OUTPUT)
As you know we are returning parameter value from above query, so we have to capture the returned value into a variable. We have to define a variable to capture the returned parameter value otherwise it will throw error.
DECLARE @ALLRECORDCOUNT INT
Stored Procedure Advanced Options
We can create stored procedure with two advanced options.
1- Encryption
Encryption – when we create any procedure with encryption option then its definition information will be hidden, which means No user can see stored procedure’s definition from the system tables.
Example- I am modifying the previously created stored procedure with encryption option.
ALTER PROCEDURE EMPLIST WITH ENCRYPTION
We have created the stored procedure with encryption option, now no user can see the definition of stored procedure using SP_HELPTEXT system stored procedure.
EXEC SP_HELPTEXT EMPLIST
Recompile – Database engine always cache execution plan when we execute stored procedure. When we create stored procedure with RECOMPILE option it indicates that database engine will not cache a plan for this procedure and every time procedure is compiled at run time. To instruct the database engine to discard plans for the individual queries inside a stored procedure, use the RECOMPLIE query hint.
ALTER PROCEDURE EMPLIST WITH ENCRYPTION
Other Stored Procedure Examples
EXAMPLE 1 : ADDING TWO NUMBERS
USE TEACHMESQLSERVER
EXAMPLE 2: INSERTING RECORDS IN EMP TABLE USING STORED PROCEDURE.
USE TEACHMESQLSERVER
We can drop Stored Procedure using Drop command, using the below syntax.
DROP PROCEDURE ProcedureName
i.e DROP PROCEDURE EMPLIST
How to do Error Handling in Stored Procedure?
What are the advantages of Stored Procedure?
What is the difference between Stored Procedure and User Defined Functions?
How to manage Transactions in Stored Procedure?
I have created a separate article for this topic. Please click here to understand how to manage Transactions in Stored Procedure in SQL Server?
In this article, I am giving a quick overview about Stored Procedure in SQL Server. After completing this article you will understand:
What is mean by Stored Procedure?
How to Create, Alter and Drop Stored Procedure in SQL Server?
How to do Error Handling in Stored Procedure?
What are the advantages of Stored Procedure?
What is the difference between Stored Procedure and User Defined Functions?
How to manage Transactions in Stored Procedure?
How to manage Transactions in Stored Procedure?
Please give your valuable suggestions and feedback to improve this article.
I have already discussed TSQL Programming Basics in SQL Server in my previous post; I will recommend you to go through this post before you start on Stored Procedure.
I hope you have gone through the basics of TSL Programming basics, now we can start.
As you know we have two types of blocks in TSQL programming, Anonymous Blocks and Sub-Program Blocks.
Sub Program blocks are of two types in SQL Server.
1. Procedures
In this article I will be focusing on Procedures, because both of the topics are very vast so I will be writing another article on functions.
What is mean by Stored Procedure?
A Stored Procedure is a saved collection of TSQL statement that you can reuse over and over again.
Let’s understand what does the above line mean? generally you write and use a SQL Query over and over again but instead of writing the SQL query again and again you can write once and save it in SQL Server with a name, later whenever you want you can call your stored query by its name. This sorted query will be known as Stored Procedure.
In addition stored procedures provide input and output parameters, depending on your input parameters stored procedure can return needed result set.Stored Procedure can run independently, it can be executed using EXEC or EXECUTE command. The greatest advantage of stored procedure is we can do Error handling using TRY, CATCH statements in Stored Procedure.
How to Create, Alter, Drop Stored Procedure in SQL Server?
Here we will understand how to create simple stored procedure, We will also create Stored Procedure with more advanced options later in this article.
Before I start with the example, let’s understand the syntax we use to create a simple stored procedure.
Syntax to create stored procedure
CREATE PROCEDURE <Procedure-Name> (
@parameter 1 data-type [=default] [out |output],
@parameter 2 data-type [=default] [out |output],
………
@parameter n data-type [=default] [out |output]
)
WITH <Procedure-Options>
AS
BEGIN
<SQL-Statements>
END
<Procedure-Name>: This is the procedure name you will give to store your query in SQL Server; you have to remember this name because you call the procedure using its name.
<Parameter-type>: Here you define your stored procedure parameter and parameter mode i.e. input or output.
<Procedure-Options>: There are two stored procedure options Encryption and Recompile. I will explain this with examples later in this article.
<SQL-Statements>: Here you write your SQL query which will be stored and executed on call.
Let’s create a simple Stored Procedure.
Stored Procedure can be created either by CREATE PROCEDURE or CREATE PROC, after giving the Stored Procedure name we need to use AS keyword. Just after the AS keyword we write our regular SQL query. If we have multiple statements then we keep it in BLOCK otherwise we can directly write the SQL Statement.
We need some data in our TEACHMESQLSERVER database, let’s populate some data.
If you don’t have TEACHMESQLSERVER database in your SQL Server instance then please use below query to create the database.
CREATE DATABASE TEACHMESQLSERVER
Let's populate some data.
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
CREATE TABLE EMP(EMPID INT, EMPNAME VARCHAR(MAX))
INSERT INTO EMP VALUES(1,'GHANESH'),(2,'PRASAD'),(3,'GAUTAM'),(4, 'ANVIE')
GO
SELECT * FROM EMP
As you can see from the above result set we have successfully populated the data. Suppose SELECT * FROM EMP is the query you write and run again and again. In this example I am using the simplest example to understand the concept but in real world it will be a set of SQL Statements.
Let’s create a stored procedure for this.
CREATE PROCEDURE EMPLIST AS
SELECT * FROM EMP
Congratulations you have successfully created your first stored procedure. You can find list of all stored procedure from sys.Procedures table using the below query
GO
SELECT * FROM SYS.procedures
As you can see from the result set of above query, EMPLIST is a SQL Stored Procedure. You can also find the other related information.
You can also find the list of Stored Procedures using object explorer in SQL Server Management Studio.
Follow the below path
As you can see under the Stored Procedures folder, our recently created EMPLIST stored procedure is saved.
Now let’s execute recently created EMPLIST stored procedure. We can execute stored procedure using EXEC or EXECUTE command.
EXEC EMPLIST
or
EXECUTE EMPLIST
As you can see it is returning the list of all employees present in the EMP table.
Stored Procedures with Parameter
We have already created a simple stored procedure without any parameter; we can create Procedures with parameters which mean we can pass parameters to Procedure; they are the means to pass a value to the procedure or returns from a procedure.These modes will specify whether the parameter is passed into the procedure or returned out of the procedure.
There are two modes of parameters in Stored Procedure.
1. IN MODE (DEFAULT)
2. OUT or OUTPUT MODE
IN MODE- Passes a value into the procedure for execution this is the best suitable for Constants and expressions. The value of the parameter can be changed in the program but it can’t 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 parameter to return values to the caller of the procedure. Text, ntext, Image data type parameters cannot be used as OUTPUT parameters. Stored Procedure can’t return table variable.
Now let’s create little complex stored procedure using input parameters.
1. Input parameters
1. Input parameters
Problem- How can you get employees details based on EMPID?
Solution- Yes you are correct, you can write a select statement and can use where clause on EMPID.
SQL code- SELECT * FROM EMP WHERE EMPID=1
You can modify your Stored Procedure using ALTER; you can use ALTER PROCEDURE at place of CREATE PROCEDURE.
Now let’s modify the stored procedure which takes Input parameter for EMPID and returns the result based on the input parameter.We will modify our EMPLIST stored procedure for the above problem.
ALTER PROCEDURE EMPLIST (@EMPID INT)
AS
SELECT * FROM EMP WHERE EMPID= @EMPID
You have successfully modified the EMPLIST stored procedure. As you can see from the above query your procedure is having one parameter, we have also modified the SQL statement. @EMPID parameter is passed in the SQL Query, based on this parameter value our procedure will return the result set.
Now let’s execute the procedure
GO
EXEC EMPLIST
Once you ran the above query you got an error message as shown below.
As you know we had modified our stored procedure, which is expecting the input parameter value but we didn’t give it at the time of execution.
There are multiple ways to assign parameter value, now let’s execute the procedure with input parameter value using any of the below method.
GO
EXEC EMPLIST 1
OR
EXEC EMPLIST @EMPID=1
Bingo our above procedure ran successfully for both of the above execute statements and returned the expected result set.
Default Parameter values
As you know now when you don’t give your parameter value at the time of execution it ends up with an error message. If you don’t want this to happen then you can set a default value for your input parameter. In below example we will be doing the same.
Let’s modify the EMPLIST stored procedure with input parameter default value.
ALTER PROCEDURE EMPLIST (@EMPID INT =1 )
AS
SELECT * FROM EMP WHERE EMPID= @EMPID
As you can see from the above SQL Statement this time we have given 1 value for @EMPID input parameter.
Now let’s execute the procedure using any of the below method.
GO
EXEC EMPLIST
OR
EXEC EMPLIST @EMPID=2
OR
EXEC EMPLIST Default
OR
EXEC EMPLIST @EMPID=Default
Bingo this time our above execute statement ran successfully and returned the expected result set.This time It didn’t give any error; you can change your parameter value as we did in previous exercise.
Multiple Parameters
Setting up multiple parameters is not very difficult; you have to separate your each parameter using comma separator. At the time of execution also you have to use comma separator.
2. Output Parameters
2. Output Parameters
This is another advantage of Stored Procedure. It can take input from parameter and it can return parameter. This can be helpful when you call another stored procedure that does not return any data, but returns parameter values to be used by the calling stored procedure.
Let’s understand how to create procedure with output parameters.
Problem- How to return all records count from EMP table using stored procedure?
Solution- You can create stored procedure with OUTPUT parameter mode.
SQL Code-
ALTER PROCEDURE EMPLIST (@RECORDCOUNT INT OUTPUT)
AS
SELECT @RECORDCOUNT=COUNT(*) FROM EMP
GO
As you can see from the above query, we have modified our procedure with one OUTPUT parameter. @RECORDCOUNT is returning the count of all records from EMP table.
As you know we are returning parameter value from above query, so we have to capture the returned value into a variable. We have to define a variable to capture the returned parameter value otherwise it will throw error.
DECLARE @ALLRECORDCOUNT INT
EXECUTE EMPLIST @ALLRECORDCOUNT OUTPUT
SELECT @ALLRECORDCOUNT
Stored Procedure Advanced Options
We can create stored procedure with two advanced options.
1- Encryption
2- Recompile
Encryption – when we create any procedure with encryption option then its definition information will be hidden, which means No user can see stored procedure’s definition from the system tables.
Example- I am modifying the previously created stored procedure with encryption option.
ALTER PROCEDURE EMPLIST WITH ENCRYPTION
AS
SELECT * FROM EMP
We have created the stored procedure with encryption option, now no user can see the definition of stored procedure using SP_HELPTEXT system stored procedure.
EXEC SP_HELPTEXT EMPLIST
Recompile – Database engine always cache execution plan when we execute stored procedure. When we create stored procedure with RECOMPILE option it indicates that database engine will not cache a plan for this procedure and every time procedure is compiled at run time. To instruct the database engine to discard plans for the individual queries inside a stored procedure, use the RECOMPLIE query hint.
Example- I am modifying the previously created stored procedure with recompile option.
ALTER PROCEDURE EMPLIST WITH ENCRYPTION
AS
SELECT * FROM EMP
Other Stored Procedure Examples
EXAMPLE 1 : ADDING TWO NUMBERS
USE TEACHMESQLSERVER
GO
CREATE PROCEDURE MYSECONDPROCEDURE (@X INT,@Y INT)
AS
BEGIN
DECLARE @Z INT
SET @z=@X+@Y
PRINT @Z
END
GO
EXEC MYSECONDPROCEDURE 10,20
EXAMPLE 2: INSERTING RECORDS IN EMP TABLE USING STORED PROCEDURE.
USE TEACHMESQLSERVER
GO
CREATE PROCEDURE INSERTEMP (@EMPNO INT,@EMPNAME VARCHAR (20))
AS
BEGIN
INSERT INTO EMP VALUES(@EMPNO,@EMPNAME)
END
GO
EXECUTE INSERTEMP 10,'RAKESH'
GO
SELECT * FROM EMP
We can drop Stored Procedure using Drop command, using the below syntax.
DROP PROCEDURE ProcedureName
i.e DROP PROCEDURE EMPLIST
How to do Error Handling in Stored Procedure?
I have created a separate article on error handling in Stored Procedure. Please click here to understand how to do error handling in Stored Procedure.
What are the advantages of Stored Procedure?
I have created a separate article on advantages of Stored Procedure. Please click here to understand the advantages of Stored Procedure in SQL Server.
What is the difference between Stored Procedure and User Defined Functions?
I have created a separate article for this topic. Please click here to understand what are the differences between Stored Procedure and User Defined Functions in SQL Server?
How to manage Transactions in Stored Procedure?
I have created a separate article for this topic. Please click here to understand how to manage Transactions in Stored Procedure in SQL Server?
Advantages of Stored Procedure in SQL Server
1- Reduce Network Traffic – It’s because when we create Stored Procedure our query gets stored in Database in Stored Procedure, This reduces traffic between client and server only call is send to the server to execute the store procedure and we pass only parameters value not entire SQL query, Therefore it reduces the network traffic.
2- Improve Performance- When first time you execute the stored procedure SQL Server Optimizer creates the execution plan and stores that execution plan in the cache so that whenever it will be called new execution will not be created and it will use the old execution plan stored in the cache memory. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.
3- Once modified available to all clients – because it is saved in Database with a name and you are sharing the name not the definition of stored procedure so once you make any change in the SP, it will be always available for other users who has access on it. We can add one more advantage it is easy to maintenance.
4- Improve Security- When calling a procedure over the network, only the call to execute the procedure is visible. Therefore, malicious users cannot see table and database object names, embed Transact-SQL statements of their own, or search for critical data. It also helps to guard against SQL injection attacks because it is more difficult for an attacker to insert a command into the Transact-SQL statement(s) inside the proc. If user want then he can encrypt stored procedure definition so that malicious user can’t see Create Procedure definition.
5- Reuse of Code- Write your code once store in database and use it again and again, This eliminates needless rewrites of the same code.
1- Reduce Network Traffic – It’s because when we create Stored Procedure our query gets stored in Database in Stored Procedure, This reduces traffic between client and server only call is send to the server to execute the store procedure and we pass only parameters value not entire SQL query, Therefore it reduces the network traffic.
2- Improve Performance- When first time you execute the stored procedure SQL Server Optimizer creates the execution plan and stores that execution plan in the cache so that whenever it will be called new execution will not be created and it will use the old execution plan stored in the cache memory. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.
3- Once modified available to all clients – because it is saved in Database with a name and you are sharing the name not the definition of stored procedure so once you make any change in the SP, it will be always available for other users who has access on it. We can add one more advantage it is easy to maintenance.
4- Improve Security- When calling a procedure over the network, only the call to execute the procedure is visible. Therefore, malicious users cannot see table and database object names, embed Transact-SQL statements of their own, or search for critical data. It also helps to guard against SQL injection attacks because it is more difficult for an attacker to insert a command into the Transact-SQL statement(s) inside the proc. If user want then he can encrypt stored procedure definition so that malicious user can’t see Create Procedure definition.
5- Reuse of Code- Write your code once store in database and use it again and again, This eliminates needless rewrites of the same code.
6- Helps to ensure integrity of database.
Managing Transactions in Stored Procedure
In this article, I am giving a quick overview about Managing Transactions in Stored Procedure in SQL Server. After completing this article you will understand:
How to manage Transactions in Stored Procedure in SQL Server?
Please give your valuable suggestions and feedback to improve this article.
I have already discussed about Stored Procedure and Transactions in SQL Server in my previous articles, if you have no idea about Stored Procedure and Transactions then I will recommend you to visit the article.
I hope now you are comfortable with Stored Procedure and Transactions, we can continue this article.
I would like to give a problem before we understand Transactions in Stored Procedure.
Problem- Suppose You need to transfer some money into someone else account. How can you do this?
CREATE DATABASE TEACHMESQLSERVER
IF OBJECT_ID('BANKING', 'U') IS NOT NULL
INSERT INTO BANKING VALUES(1,'GHANESH',50000)
/*Inserted Records*/
/*Creating FUNDSTRANSFER Stored Procedure to transfer the money from one account to another account*/
/*Checking the Balance before Transfering the money, then Transfering the money using Stored Procedure and checking the Balance after transfering*/
As you can see from the above result set, 5000 amount has been transferred from PRASAD account to YOGITA account.
What do you think; is this enough? My answer is Big No.
Suppose in above case if SourceID or DestID is not present then it will deduct the amount from the other account or add the amount from the other account.
As you know we have 4 accounts (1-4) in our BANKING Table, in below query I am transferring 5000 RS from account 1 to account 5. Please note account 5 is not present in the table. Let’s see what happens
Let’s execute the below code to understand the problem
SELECT * FROM BANKING
To overcome from the problem we need to use Transaction in Stored Procedure.
How to manage Transactions in Stored Procedure in SQL Server?
Let’s ALTER our Stored Procedure.
ALTER PROCEDURE FUNDSTRANSFER(@SOURCEID INT, @DESTID INT, @AMT INT)
DECLARE @COUNT1 INT, @COUNT2 INT
BEGIN TRANSACTION
UPDATE BANKING SET BALANCE=BALANCE-@AMT WHERE ACCOUNT=@SOURCEID
IF @COUNT1=@COUNT2
ELSE
BEGIN
EXEC FUNDSTRANSFER 1,5, 5000
As you can see this time, it didn't deduct the money. Transaction checks whether amount was deducted and added to the account, if not so then it fails.
In this article, I am giving a quick overview about Managing Transactions in Stored Procedure in SQL Server. After completing this article you will understand:
How to manage Transactions in Stored Procedure in SQL Server?
Please give your valuable suggestions and feedback to improve this article.
I have already discussed about Stored Procedure and Transactions in SQL Server in my previous articles, if you have no idea about Stored Procedure and Transactions then I will recommend you to visit the article.
I hope now you are comfortable with Stored Procedure and Transactions, we can continue this article.
I would like to give a problem before we understand Transactions in Stored Procedure.
Problem- Suppose You need to transfer some money into someone else account. How can you do this?
Solution- Yes, you are correct. You can write SQL query or stored procedure to deduct the money from the person’s account that is transferring and add the deducted money into the person’s account that is receiving the money.
SQL Code-
CREATE DATABASE TEACHMESQLSERVER
USE TEACHMESQLSERVER
GO
/*Creating BANKING Table*/
IF OBJECT_ID('BANKING', 'U') IS NOT NULL
DROP TABLE BANKING
CREATE TABLE BANKING (ACCOUNT INT, NAME VARCHAR(MAX), BALANCE MONEY)
GO
INSERT INTO BANKING VALUES(1,'GHANESH',50000)
INSERT INTO BANKING VALUES(2,'PRASAD',25000)
INSERT INTO BANKING VALUES(3,'YOGITA',35000)
INSERT INTO BANKING VALUES(4,'GAUTAM',4000)
GO
/*Inserted Records*/
SELECT * FROM BANKING
GO
/*Creating FUNDSTRANSFER Stored Procedure to transfer the money from one account to another account*/
CREATE PROCEDURE FUNDSTRANSFER (@SOURCEID INT, @DESTID INT, @AMT INT)
AS
BEGIN
UPDATE BANKING SET BALANCE = BALANCE -@AMT WHERE ACCOUNT=@SOURCEID
UPDATE BANKING SET BALANCE = BALANCE+@AMT WHERE ACCOUNT=@DESTID
END
GO
/*Checking the Balance before Transfering the money, then Transfering the money using Stored Procedure and checking the Balance after transfering*/
SELECT * FROM BANKING
EXEC FUNDSTRANSFER 2,3, 5000
SELECT * FROM BANKING
GO
As you can see from the above result set, 5000 amount has been transferred from PRASAD account to YOGITA account.
What do you think; is this enough? My answer is Big No.
Suppose in above case if SourceID or DestID is not present then it will deduct the amount from the other account or add the amount from the other account.
As you know we have 4 accounts (1-4) in our BANKING Table, in below query I am transferring 5000 RS from account 1 to account 5. Please note account 5 is not present in the table. Let’s see what happens
Let’s execute the below code to understand the problem
SELECT * FROM BANKING
EXEC FUNDSTRANSFER 1,5, 5000
SELECT * FROM BANKING
GO
As you can see from the above result output 5000 Rs has been deducted from account 1 (GHANESH) but it was not added into any other account. It happened because we were transferring the amount into account 5 which is not present.
To overcome from the problem we need to use Transaction in Stored Procedure.
How to manage Transactions in Stored Procedure in SQL Server?
To manage the Transaction first we need to identify which statement is executed and which failed; for this we will use @@ROWCOUNT function.
Let’s ALTER our Stored Procedure.
ALTER PROCEDURE FUNDSTRANSFER(@SOURCEID INT, @DESTID INT, @AMT INT)
AS
BEGIN
DECLARE @COUNT1 INT, @COUNT2 INT
BEGIN TRANSACTION
UPDATE BANKING SET BALANCE=BALANCE-@AMT WHERE ACCOUNT=@SOURCEID
SET @COUNT1=@@ROWCOUNT
UPDATE BANKING SET BALANCE=BALANCE+@AMT WHERE ACCOUNT=@DESTID
SET @COUNT2=@@ROWCOUNT
IF @COUNT1=@COUNT2
BEGIN
COMMIT
PRINT 'AMOUNT HAS BEEN TRANFERRED'
END
ELSE
BEGIN
ROLLBACK
PRINT 'AMOUNT TRANFERED FAILED'
END
END
Let’s try to transfer amount to account 5
EXEC FUNDSTRANSFER 1,5, 5000
As you can see this time, it didn't deduct the money. Transaction checks whether amount was deducted and added to the account, if not so then it fails.
MS-SQL : Stored procedure coding standard/check list
It is always better to have check list for creating stored procedure in SQL server. Most people may not think that it is really required to have; yes I agreed that for experienced developer it may not be required because by default they use the entire coding standard because of their experience. But during my 8 years of experience I have seen many of experienced developers also forgetting basic principles and hence this post.
1. Procedure name should be as per the standard provided by the client or decided by the project team.
2. Proper comments to understand the functionality.
3. Name of the preson created, date, purpose,sample execution statement at the beginning as shown in fig 1.
4. All the syntax should be in CAPS
5. Align the code with right indentation- this helps to understand the code very easily
6. Proper exception handling as shown in Fig 2
7. Raise error appropriately to help the user understand what exactly happened.
8. Keep declaration, initiation etc. in separate section of procedure and do not mix the same.
 |
| Fig 1 |
 |
| Fig 2 |
Stored Procedure's :
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. Stored Procedures in SQL Server are similar to procedures in other programming languages in that they can:
- Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
- Contain programming statements that perform operations in the database, including calling other procedures.
- Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
- They allow modular programming.
- They allow faster execution.
- They can be reduce network traffic.
- They can be used as security mechanism.
Type of Stored Procedure's :
SQL Server supports five types of stored procedures. They are:
System Stored Procedures (sp_)
Many administrative and informational activities SQL Server can be performed through system stored procedures. These system stored procedures are stored in the Master database and are identified by the sp_prefix. They can be executed from any database.
Local Stored Procedures (sp_)
These procedures will be created in the user database. The user who create the will become the owner for that procedure.
Temporary Stored Procedures (sp_)
Temporary stored procedures are stored in tempdb database. They can be used in the case where an application builds dynamic Transact-SQL statements that are executed several times. Instead of recompiling the T-SQL statements each time, a temporary stored procedure can be created and compiled on the first execution, then execute the precompiled plan multiple times. The temporary stored procedures can be local or global.
Remote Stored Procedures (sp_)
They are legacy feature of SQL Server. Their functionality in T-SQL is limited to executing a stored procedure on a remote SQL Server installation. The distributed queries in SQL Server support this ability along with the ability to access tables on linked OLEDB data sources directly from local T-SQL statements.
Extended Stored Procedures (sp_)
These are dynamic link libraries (DLLs) that SQL Server can dynamically load and execute. These procedures run directly in the address space of SQL Server and are programmed using SQL Server Open Data Services API. They are identified by the xp_prefix.
Creating a Stored Procedure :
The stored procedures can be created using the CREATE PROCEDURE statment.
Syntax :
1
2
3
4
5
6
7
8
9
10
11
| CREATE PROCEDURE procedure_name [( @parameter1 datatype [OUTPUT]), ( @parameter2 datatype..........) AS BEGIN SQL - Statements [RETURN] END |
SQL SERVER Provide Two type of Parameters :
- Input Parameters
- Output Parameters
Input Parameters will be used to pass a value using the procedure call. Based on this value, the procedure execution will be done.
Output Parameters will be used to assign a value inside the procedure definition which can be used by the calling program. Before execution of the stored procedure, output parameter doesn't contain any value. Once the procedure execution completes, now the output parameter gets some value.
Output Parameters will be used to assign a value inside the procedure definition which can be used by the calling program. Before execution of the stored procedure, output parameter doesn't contain any value. Once the procedure execution completes, now the output parameter gets some value.
Example : Creating a Procedure to insert values into products table :
1
2
3
4
5
| Create procedure pInsertProductst (@pid int, @pn char(10), @pqty int) AS BEGIN Insert into Products values (@pid, @pn, @pqty) END |
Executing the Procedure :
We can execute a procedure will EXEC statement by specifying the procedure name and parameters list. If procedure call is only the statement to be executed, then no need to use exec statement. We can directly use procedure name and parameters list. Whereas, if we are calling the procedure from another procedure of T-SQL program, then we must use the EXEC statement to call the procedure.
1
2
3
4
5
6
7
8
9
| Syntax :-====== EXEC < Proc.Name > [Val1, Val2, Val1 output,...]Example :-======= exec PInsertProducts (1, 'P1', 200) |
Write a program to calculating the following details for a given employee number.
- Basic Salary
- Benefits (Commission Percentage on salary)
- Net Salary
1. Creating a procedure to calculate Benefits
1
2
3
| create procedure emp_benefits(@eno int, @add int output) as select @add = comm * sal * 0.01 from emp where empid = @eno |
2. Creating a procedure to calculate Net Salary
1
2
3
4
5
6
7
8
9
| create procedure emp_netsalary(@eid int) as BEGIN declare @bsal int, @add int, @nsal int exec emp_benefits @eid, @add output select @bsal = sal from emp where empid = @eid set @nsal = @bsal + @add select Empid = @eid, BasicSalary = @bsal, Benefits = @add, NetSalary = @nsal END |
Error Handling in Stored Procedure :
In order to not get any runtime errors it is important to handle errors in stored procedure. Using a 'Begin Try' and 'Begin Catch' blocks error handling can be done in SQL Server.
1
2
3
4
5
6
7
8
9
10
11
12
13
| create procedure < procedurename > (Parameter1,.....) as BEGIN Begin Try ------------- < Executable statements > End Try Begin Catch ---- Error Handling End Catch End |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| Create procedure PInsertProductst(@pid int, @pn char(10), @pqty int) AS BEGIN Begin Try Insert into products values(@pid, @pn, @pqty) End Try Begin Catch If Error_Number() = 2627 Print 'Duplicate product id value entered' Else Print Error_Message() End Catch End
STORED PROCEDURE AND RULES FOR CREATING STORED PROCEDURE IN T-SQL
Stored Procedure:
To create Stored procedure, the CREATE PROCEDURE Statement is used. Stored procedures are created in the current database unless a temporary stored procedure is created in tempdb. Because stored procedure are precompiled, they usually provide the best performance of any type of query.
Rules for creating Stored procedure:
i) The name must follow the rules for identifiers.
ii) Referenced objects must exist when your stored procedure runs.
iii) Object with the same name in a single stored procedure can’t be created and then drop or re-create.
iv) Temporary tables can be referenced within the stored procedure. Local temporary tables disappear when the procedure ends.
v) Upto 1024 parameters can be allowed.
vi) Procedure can be nested within procedure upto 32 levels deep.
vii) Stored Procedure can’t have the following T-SQL create statement in them:
Ø CREATE DEFAULT
Ø CREATE RULE
Ø CREATE PROCEDURE
Ø CREATE TRIGGER
Ø CREATE VIEW
Stored Procedure| Database Stored Procedures| SQL Stored Procedures
A stored procedure is a set of SQL statements that can be stored in the server.
Benefits of Stored Procedures 1. Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly. 2. Reduced client/server traffic. If network bandwidth is a concern in yourenvironment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire. 3. Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time. 4. Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions. Syntax: CREATE PROCEDURE [procedure name] (@parameter1 type, @parameter2 type) AS Sql statements……… BEGIN END ……………. GO To declare variables: DECLARE Eg: DECLARE @temp int To assign values: SET Eg: SET @temp = 0 To return a value: CREATE PROCEDURE [proc name] (@p1 type, @p2 type output) AS …… GO Eg: Create Procedure GetAuthorsByLastName1 (@au_lname varchar(40), @RowCount int output) as select * from authors where au_lname like @au_lname; /* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ select @RowCount=@@ROWCOUNT Error Handling: @@Error is used. Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement. To Execute in query analyzer: Exec [procedure name] [parameters]To call from ASP page:Rs.Open "exec [procedure name] [parameters]", conn To write comments: /* */ Stored Procedure
Definition: One or more statements that has been given a name and stored within database.
Advantage:
Type of SPs
· System SPs
o Starting with sp_
o E.g. (sp_helptext, sp_executesql, sp_tables, sp_rename, etc.)
o Stored in master db
o We can create UDSP with the same name but it will never be executed even if we execute like DBName.SchemaName.ProcName. SQL server internally first checks it in master db and if it finds an SP with the same name, it will execute that SP
· User Defined SPs
o Created by User
o Nested SPs will be upto 32 levels
o Excepts input and output parameter, also excepts UDT
o You cannot have Go, Use DB, Create Proc/View/Function inside the body of SP
· Extended SPs
o Starting with XP_
o E.g. (XP_SendMail, etc.)
o It is for all System level functions
· Local Temporary SPs
o Starting with #
o Local scope
· Global Temporary SPs
o Starting with ##
o Global scope until SQL server is up
-- Create a Proc with OUTPUT param
CREATE PROC Proc1 (@a int, @b int, @c int OUT)
AS
BEGIN
BEGIN TRY
SET @c = @a + @b
END TRY
BEGIN CATCH
EXEC GenericErrorProc
END CATCH
END
-- Exec usage of the proc
DECLARE @c int
EXEC Proc1 1,2,@c out
SELECT @c
-- Create a proc which will return a table
CREATE PROC Proc1
AS
BEGIN
BEGIN TRY
SELECT A,B FROM TableName
END TRY
BEGIN CATCH
EXEC GenericErrorProc
END CATCH
END
-- Exec usage of SP
create table #temp (a int, b int)
insert into #temp (a) exec proc1
Something Extra
SP_PROCOPTION (‘SPName’, ‘startup’,’true’)
Creating Index within Stored Procedure
Introduction
One of my friends asking a question is it better to create Index within Stored Procedure depends on the columns used in stored procedure to improve the Join performance and Drop the stored procedure after getting the desired output and just before the ending of Stored Procedure.
Technical Answer
Yes we can create Index within the Stored Procedure
Now we have to think what we answer it
Technically it is possible what my friend is mentioned in the scenario. But performance wise it’s again a problematic solution. That’s means to solve something we again create another problem. To understand it properly let’s take an example of such kind of Stored Procedure.
IF OBJECT_ID(N'[dbo].[sproc_StudentDetails]', N'P')IS NOT NULL
DROP PROCEDURE [dbo].[sproc_StudentDetails];
GO
CREATE PROCEDURE [dbo].[sproc_StudentDetails]
(
@p_StdClass INT
)
AS
BEGIN
-- Creating Non Clustered Index on IDNO
CREATE NONCLUSTERED INDEX IX_NC_IDNO_StdDtl
ON tbl_StudentDetails(IDNO);
CREATE NONCLUSTERED INDEX IX_NC_IDNO_StdMarks
ON tbl_StudentMarks(IDNO);
-- Making JOIN on IDNO for Performance Reason
SELECT a.Roll, a.StdName, b.Marks1, b.Marks2
FROM tbl_StudentDetails AS a
INNER JOIN tbl_StudentMarks AS b ON a.IDNO =b.IDNO;
-- Droping the Index
DROP INDEX IX_NC_IDNO_StdDtl ON tbl_StudentDetails;
DROP INDEX IX_NC_IDNO_StdMarks ON tbl_StudentMarks;
END
GO
Here in this example if we look carefully the No clustered Index is created and after successful joining it again drops.
It is technically perfect.
So what the Problem is
SELECT a.Roll, a.StdName, b.Marks1, b.Marks2
FROM tbl_StudentDetails AS a
INNER JOIN tbl_StudentMarks AS b ON a.IDNO =b.IDNO;
The performance of the JOIN condition is increased due to non clustered index. So we think that we got it.
NO it is NOT. We have to understand something before implemented it.
When the Index Created the Index table and the Statistical table both are updated, so making index within stored procedure again takes a long time to create index.
By the index creation we solve the Join performance but Index creation time is again a pain faction and slowdown the performance of Stored procedure.
Hope you like it
Finding Dependency In Stored Procedure
Introduction
Here in this article we are trying to discuss about the finding reference object within stored procedure and also finding the calling procedure references. Hope you like it and it will be informative.
What we Want
Developers are writing several stored procedure almost every day. Sometimes developers need to know about the information such as what object is used within the stored procedure or from where (SP) the specified stored procedure call. This is the vital information for the developer before working on a particular stored procedure.
Here we are representing a pictorial diagram to understand the nature of implementation.
Now we have to answer some question
1. What are the DB Object used in Stored Procedure1 and there type.
2. In case of Store Procedure3 which procedure calls the Store Procedure3
So we are not going to read the Stored Procedure to find the answer. Suppose the each procedure have more than 3000 line.
How we solve the answer
To solve the answer first we take the example and create an example scenario to understand it.
-- Base Table
CREATE TABLE T1
(EMPID INT, EMPNAME VARCHAR(50));
GO
CREATE TABLE T2
(EMPID INT, EMPNAME VARCHAR(50));
GO
--1
CREATE PROCEDURE [dbo].[Procedure1]
AS
BEGIN
SELECT * FROM T1;
SELECT * FROM T2;
EXEC [dbo].[Procedure3];
END
GO
--2
CREATE PROCEDURE [dbo].[Procedure2]
AS
BEGIN
EXEC [dbo].[Procedure3];
END
GO
--3
CREATE PROCEDURE [dbo].[Procedure3]
AS
BEGIN
SELECT * FROM T1;
END
GO
Now we are going to solve the question
What are the DB Object used in Stored Procedure1 and there type.
sp_depends Procedure1
In case of Store Procedure3 which procedure calls the Store Procedure3
SELECT OBJECT_NAME(id) AS [Calling SP]
FROM syscomments
WHERE [text] LIKE '%Procedure3%'
GROUP BY OBJECT_NAME(id);
Hope you like it.
Scope of Temp (#) Table within Stored Procedure
Introduction
I personally do not like the system of nesting Temporary table within Different Stored procedure. But lot of developer prefers it. Here in this article we are going to demonstrate it.
Nested Stored Procedure With Temporary Table
First we understand the structure.
Hope you understand it by the above pictorial diagram. The above diagram is in correct format.
Here I am showing another diagram bellow 
The second diagram represents showing the Wrong implementation. So the Temporary table must present in the main calling stored procedure, the first pictorial diagram represent that.
Now make some Practical Work
The correct format:
-- 1
CREATE PROCEDURE [dbo].[Procedure1]
AS
BEGIN
CREATE TABLE #T1
(
IDNO INT,
EMPNAME VARCHAR(50)
);
EXEC [dbo].[Procedure2];
EXEC [dbo].[Procedure3];
END
GO
-- 2
CREATE PROCEDURE [dbo].[Procedure2]
AS
BEGIN
INSERT INTO #T1
(IDNO, EMPNAME)
VALUES(1, 'Joydeep Das'),(2, 'Deepasree Das');
END
GO
-- 2
CREATE PROCEDURE [dbo].[Procedure3]
AS
BEGIN
SELECT * FROM #T1;
END
GO
-- Executing
EXEC [dbo].[Procedure1];
Output:
IDNO EMPNAME
----------- --------------------------------------------------
1 Joydeep Das
2 Deepasree Das
Now the Wrong Format:
-- 1
CREATE PROCEDURE [dbo].[Procedure1]
AS
BEGIN
EXEC [dbo].[Procedure2];
EXEC [dbo].[Procedure3];
EXEC [dbo].[Procedure4];
END
GO
-- 2
CREATE PROCEDURE [dbo].[Procedure2]
AS
BEGIN
CREATE TABLE #T1
(
IDNO INT,
EMPNAME VARCHAR(50)
);
END
GO
-- 3
CREATE PROCEDURE [dbo].[Procedure3]
AS
BEGIN
INSERT INTO #T1
(IDNO, EMPNAME)
VALUES(1, 'Joydeep Das'),(2, 'Deepasree Das');
END
GO
-- 4
CREATE PROCEDURE [dbo].[Procedure4]
AS
BEGIN
SELECT * FROM #T1;
END
GO
-- Executing
EXEC [dbo].[Procedure1];
Output:
Msg 208, Level 16, State 0, Procedure Procedure3, Line 6
Invalid object name '#T1'.
Msg 208, Level 16, State 0, Procedure Procedure4, Line 6
Invalid object name '#T1'.
Hope you like it.
Type of SP
There are many type of stored procedure, but broadly we can classify it into three categories.
1. User define stored procedure
2. External stored procedure
3. System stored procedure
User define stored procedure:
This type is mostly a routine or bundle of individual work grouping together to perform some set of works.
It again divided into 2 types
a. Transact-SQL
b. CLR
In transact-SQL contain Transact-SQL statements that works together to performs a specific tasks. Such as, a stored procedure to perform some DML or DDL activity.
A CLR stored procedure is a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters. They are implemented as public, static methods on a class in a .NET Framework assembly
External Stored procedure:
It is used to create external routines by any kind of programming language like C.
External stored procedures are a DLL that dynamically load by SQL server and run.
External stored procedures run dynamically in the same memory space where the program that runs the external stored procedures API runs.
It generally takes xp_ prefix.
System Stored procedures:
Many administrative tasks is performed by it.
For example sys.sp_helptrigger is a system procedure. The system stored procedure is always prefixed by sp_. They logically appear at sys schema of every systems and user define database.
For better understanding please refer to MSDN blog
http://msdn.microsoft.com/en-us/library/ms187644(v=sql.90).aspx
Auto generated SP with Parameters
When we are thinking about a procedure to insert data in a specified table, we must use all the columns name of the table as parameter to supply the value on the procedure.
Sometimes the table have a huge number of columns and the developer are bored to make parameters in all the columns and there data types and whether the columns is taking NULL or NOT NULL values.
In this article I am providing a simple stored procedure to generate a frame of stored procedure. My stored procedure takes two arguments. One is the SP name and another is the table name where we want to insert the data or make any operations.
The main thing is that it generates the parameters automatically from supplied table objects.
The Stored procedure is ready to use and you can enjoy it by just copy and paste.
IF EXISTS (SELECT *
FROM sysobjects
WHERE type = 'P'
AND name = 'up_RUN_SP_FRAME_CREATOR')
BEGIN
DROP PROCEDURE up_RUN_SP_FRAME_CREATOR
END
GO
CREATE Procedure [dbo].[up_RUN_SP_FRAME_CREATOR]
(
@p_SPName VARCHAR(30) = NULL,
@p_TBLName VARCHAR(MAX) = NULL
)
AS
DECLARE @sqlString AS NVARCHAR(MAX)
CREATE TABLE #Tbl
(TblTxt VARCHAR(MAX))
BEGIN
SET NOCOUNT ON;
IF ISNULL(@p_SPName,'')='' OR ISNULL(@p_TBLName,'')=''
BEGIN
PRINT ('Please supply All the Parameters Value')
GOTO WayOut
END
INSERT INTO #Tbl
SELECT 'CREATE PROCEDURE '+ @p_SPName
UNION ALL
SELECT '('
UNION ALL
SELECT '@p_' + column_name + REPLICATE(' ', 40-LEN(column_name))
+ data_type + CASE WHEN data_type LIKE '%char' OR data_type LIKE '%binary'
THEN ' (' + CASE WHEN CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH) = '-1'
THEN 'max'
ELSE CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH)
END + ')'
WHEN data_type IN ('decimal', 'numeric')
THEN ' (' + CONVERT(VARCHAR, NUMERIC_PRECISION) + ', '
+ CONVERT(VARCHAR, NUMERIC_SCALE) + ')'
WHEN data_type = 'float' THEN '(' + CONVERT(VARCHAR, NUMERIC_PRECISION) + ')'
ELSE ''
END
+ CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT' ELSE '' END + ' NULL'
FROM information_schema.columns
WHERE table_name = @P_TBLName
UNION ALL
SELECT ') AS WITH ENCRYPTION'
UNION ALL
SELECT 'DECLARE @.... <dataTypes>'
UNION ALL
SELECT 'DECLARE @.... <dataTypes>'
UNION ALL
SELECT 'DECLARE @.... <dataTypes>'
UNION ALL
SELECT 'BEGIN'
UNION ALL
SELECT '<Body.....>'
UNION ALL
SELECT 'END'
SELECT * FROM #Tbl
WayOut:
END
GO
To run this stored procedure
EXEC up_RUN_SP_FRAME_CREATOR
@p_SPName='my_Sp',
@P_TBLName='my_Table'
Hope you like it.
Stored Procedure and Re-Compiling
Each time when we call the stored procedure the SQL server does not create a new access plan to retrieve the information of the database. The query of the stored procedure is optimized when it is compiled.
If we make some changes in the table structure or create new indexes which may optimize the data retrieval, but already compiled store procedure may lose efficiency. By re-compiling the stored procedure we may optimize the query.
So the re-compiling of stored procedure is important when we change any related objects in that is used by stored procedure to make new execution plan for it and optimize the performance.
In this article, I am trying to illustrate how we can recompile the stored procedure to optimize performance and set new execution plan for it.
There are three way to re-compile the store procedure.
1. By sp_recompile [stored procedure name]
It is forcing the stored procedure to re-compile next time it is executed.
For example
EXEC sp_recompile [SP Name]
2. WITH RECOMPILE option
Create a stored procedure specifying WITH RECOMPILE option. If WITH RECOMPILE is specified SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Ideally, we should not use this option because by using this option, you lose most of the advantages you get by substituting SQL queries with the stored procedures. However, we can use this option during early development stages of the project where database structure is still evolving and stored procedures should be recompiled as a result of these changes.
CREATE PROCEDURE usp_MyProcedure
WITH RECOMPILE
AS
SELECT SampleName,
SampleDesc
FROM SampleTable
GO
3. You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.
For example:
EXEC usp_MyProcedure WITH RECOMPILE
Stored Procedure Performance
Performance is the one of the key factor that I always afraid of when I am writing any complex query. The slow performance came from various factors and we always trying to solve this issue by day to day basis.
For the performance related Issue, I am always welcome everyone to write something and make there valuable suggestion to help others.
Here I am collecting some facts that directly related to stored procedure. By maintain this technique we can save some executions time.
|
