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?
I hope you enjoyed the article don't forget to share on Facebook.
Keep Learning !
