Monday, September 8, 2014

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 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?

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

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

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 !

No comments: