Stored Procedure Tutorial

Introducing to Stored Procedure
Stored procedure by definition is a segment of code which contains declarative or procedural SQL statement. A stored procedure is resided in the catalog of the database server so we can call it from a trigger, another stored procedure or even from a program.

As the definition above, the stored procedure can contains any SQL statement like INSERT, UPDATE and DELETE or any SQL data definition like CREATE TABLE, ALTER TABLE and etc. Furthermore, a stored procedure also supports procedure statements such as IF, WHILE to make it as powerful as a normal programming language.

Using Stored procedure has several advantages :

It is used to increases the performance of application because when we create stored procedure, they are compiled and stored in database catalog. Later when applications call them, they are generally executed faster than uncompiled SQL statements which are sent from the applications.
The network traffic between application server and database server is also signification reduced because the applications don't have to send such long and uncompiled SQL statements to the server to get the data back.
Stored procedures can be used for database security purpose because each store procedure can have its own database privileges.
One of the most advantage of stored procedure is code reusability. Once created, a store procedure can be reused over and over again by multiple applications.
It is the best to illustrate the ability of stored procedure by showing examples, you can follow via this tutorial to understand more about stored procedure. We will use Microsoft SQL Server to demonstrate stored procedure, you can also use MySQL with a change a little bit because of specification of each database server is different. Continue reading on getting started with stored procedure



Getting Started with Stored Procedure
Writing the first stored procedure
Here is the first stored procedure source code

view plaincopy to clipboardprint?
CREATE PROCEDURE Delete_Employee  
   (@EmployeeId INT)  
AS 
 
BEGIN 
   DELETE FROM  Employees  
   WHERE  EmployeeId = @EmployeeId;  
END 

CREATE PROCEDURE Delete_Employee
   (@EmployeeId INT)
AS

BEGIN
   DELETE FROM  Employees
   WHERE  EmployeeId = @EmployeeId;
END
A stored procedure must contains at least three parts: stored procedure name, parameter list and its body.

The CREATE PROCEDURE is similar to CREATE TABLE or INDEX statement. It is actually an SQL statement. The CREATE PROCEDURE will force the database server add the stored procedure to the database catalog. The name of stored procedure is followed after the CREATE PROCEDURE statement, in this case it is Delete_Employee. It would be the best that the name is meaningful and follows by the naming convention of the database server specification, for example each stored procedure should begin with "sp". For most Relation database product, the name of stored procedure must be unique.

The second part is parameter list, in this case the list contains only one parameter @EmployeeId (the employee identity). Microsoft SQL Server required prefix @ for every parameters and variables of stored procedure. Followed each parameter is its type, in this case, its type is integer.

The stored procedure body starts with keywords BEGIN and ends with keyword END. In this example the body is very simple. It deletes employee by employee identity.

When all syntax statements inside body are correct, the database server will store the stored procedure in its catalog for reusing later by another stored procedure or programs.

Calling a stored procedure
We can call a stored procedure from the console window, from another stored procedure or from a program which can access database server. The syntax of calling a stored procedure is simple as follows:


 

EXEC spName(parameter_value_list)
The EXEC statement is used to invoke a stored procedure, after that the stored procedure name is followed in our procedure example to delete an employees with identity is 8 we can call its by following statement:


EXEC Delete_Employee(8) 

EXEC Delete_Employee(8)
If a stored procedure has more than one parameters, the values of them can be passed to it and separated by a comma.

As you see writing and calling a stored procedure is very simple and easy. In the following tutorial we will show you the feature and syntax of a stored procedure along with statement which can be used inside the body so you can empower its power. Continue reading on parameter list in stored procedure



Parameter List in Stored Procedure
A stored procedure can have zero, one or more than one parameters. If a stored procedure has more than one parameter, each one must be separated by a comma.

A parameter can be described by three parts : its name, its data type and its type.

The name of parameter in Microsoft SQL Server must has @ sign as the prefix otherwise the database server will notify the error and of course the stored procedure cannot be saved in the database catalog.

Following the name is the parameter data type. The data type of the stored procedure can be any valid data type which are predefined in the database server. If you specify the size of the data type you can do it as follows:

@parameter VARCHAR(255) 

@parameter VARCHAR(255)
The third part of a parameter is its types. This part is optional, by default it is the IN. There are three parameter types : IN, OUT and INOUT. As you guess IN is abbreviation of input, OUT is abbreviation of output and INOUT is combined of both. With input parameter you can pass the value in the stored procedure, in the last example we pass the employee identity in and delete the employee based on it. Output parameter allows you get the value back from the stored procedure, for example you may write a stored procedure to insert an employee and get its id back to the application to use in another part of the program. The INOUT parameter type as its name suggested can act as both types of parameter.

In some SQL database sever product, it does not require parameter has @ sign as prefix so you should be careful that the name of the parameter must not be equal to the name of column otherwise you may face up to the disaster without any warning or error message from the database server.

In Microsoft SQL Server 2005, it allows you to specify the default value of the parameter. It is a big plus. Because you can call a stored procedure without passing parameters, it will use default values. Here is syntax of default value of parameter.


@parameter DataType(Size) = Default_Value 

@parameter DataType(Size) = Default_Value
In our example we can modify to use default value for stored procedure as follows: view plaincopy to clipboardprint?
CREATE PROCEDURE Delete_Employee  
   (@EmployeeId INT = 0)  
AS 
 
BEGIN 
   DELETE FROM  Employees  
   WHERE  EmployeeId = @EmployeeId;  
END 

CREATE PROCEDURE Delete_Employee
   (@EmployeeId INT = 0)
AS

BEGIN
   DELETE FROM  Employees
   WHERE  EmployeeId = @EmployeeId;
END
When you call it without passing parameter value, it will delete the employee with the identity is zero.

SQL stored procedure parameter list is simple and easy to grasp? let's move to the body part of the stored procedure.







Body in Stored Procedure
The body part of a stored procedure is where you can put your business logic codes inside to execute it as you want it to do. A stored procedure body always begin with BEGIN and END keywords. Inside the body, you can put the declarative SQL statements or procedure call like calling other stored procedures. Beside that, you can also use procedure statement like IF, WHILE; you can declare local variables and use them inside the stored procedure. Here is the general stored procedure syntax :


<create procedure statement> ::=  
   CREATE PROCEDURE <procedure name> ( [  
 <parameter list> ] ) AS 
      <procedure body>  
 
<procedure body> ::= <begin-end block>  
 
<begin-end block> ::=  
   [ <label> : ] BEGIN <statement list> END [   
<label> ]  
 
<statement list> ::= { <body statement> ; }...  
 
<statement in body::=  
   <declarative statement> |  
   <procedural statement>  
 
<declarative statement> ::=  
   <EXEC statement>              |  
   <CLOSE statement>             |  
   <COMMIT statement>            |  
   <DELETE statement>            |  
   <EXECUTE immediate statement> |  
   <FETCH statement>             |  
   <INSERT statement>            |  
   <LOCK TABLE statement>        |  
   <OPEN statement>              |  
   <ROLLBACK statement>          |  
   <savepoint statement>         |  
   <SELECT statement>            |  
   <SELECT INTO statement>       |  
   <SET statement>               |  
   <SET transaction statement>   |  
   <start -transaction statement> |  
   <UPDATE statement>  
 
<procedural statement> ::=  
   <BEGIN-END block>             |  
   <EXEC statement>              |  
   <CLOSE statement>             |  
   <DELCARE condition statement> |  
   <DELCARE cursor statement>    |  
   <DELCARE handler statement>   |  
   <DELCARE variable statement>  |  
   <FETCH cursor statement>      |  
   <flow control statement>      |  
   <OPEN cursor statement>       |  
   <SET statement> 

<create procedure statement> ::=
   CREATE PROCEDURE <procedure name> ( [
 <parameter list> ] ) AS
      <procedure body>

<procedure body> ::= <begin-end block>

<begin-end block> ::=
   [ <label> : ] BEGIN <statement list> END [
<label> ]

<statement list> ::= { <body statement> ; }...

<statement in body::=
   <declarative statement> |
   <procedural statement>

<declarative statement> ::=
   <EXEC statement>              |
   <CLOSE statement>             |
   <COMMIT statement>            |
   <DELETE statement>            |
   <EXECUTE immediate statement> |
   <FETCH statement>             |
   <INSERT statement>            |
   <LOCK TABLE statement>        |
   <OPEN statement>              |
   <ROLLBACK statement>          |
   <savepoint statement>         |
   <SELECT statement>            |
   <SELECT INTO statement>       |
   <SET statement>               |
   <SET transaction statement>   |
   <start -transaction statement> |
   <UPDATE statement>

<procedural statement> ::=
   <BEGIN-END block>             |
   <EXEC statement>              |
   <CLOSE statement>             |
   <DELCARE condition statement> |
   <DELCARE cursor statement>    |
   <DELCARE handler statement>   |
   <DELCARE variable statement>  |
   <FETCH cursor statement>      |
   <flow control statement>      |
   <OPEN cursor statement>       |
   <SET statement>
With the BEGIN and END keword you can label the block of code inside the body. You can have one or more blocks, each block can be nested each other. Labeling the block has its own advantages. For example, it make your code more clear when you have mutilple blocks. Let's get your hand with some source code to demonstrate the stored procedure body.

Imagine we have employee table, in one day the table may have many records and it is very costly to get all the data from it and display them in our application. It would be nice if we can provide pagination feature for application to select needed records it needs to reduce the traffic between the database server and application server. Here is stored procedure to make it possible:

view plaincopy to clipboardprint?
CREATE PROCEDURE GetEmployeePaged  
    @PageSize int = 10,-- pagesize  
    @CurrentPage int = 1,-- current page no  
    @ItemCount int output -- total employee found  
AS 
BEGIN 
    -- declare local variables for pagination  
    DECLARE @UpperBand INT,  
            @LowerBand INT 
 SET @LowerBand = (@CurrentPage - 1)* @PageSize  
    SET @UpperBand = @CurrentPage* @PageSize + 1  
      
    -- assign itemcount output parameter  
    SET @ItemCount = (  
        SELECT COUNT(employeeId)   
        FROM employees  
    )  
 
    -- create temporary table to store paged data  
    CREATE TABLE #ALLROW(  
           RowID INT PRIMAY KEY IDENTITY(1,1),  
            EmployeeId INT,  
            Name VARCHAR(255),  
            salary DECIMAL(7,2)  
        )  
 
    -- insert data into the temporary table  
    INSERT INTO #ALLROW  
    SELECT EmployeeId, Name, salary  
    FROM employees  
 
    -- get paged data  
 SELECT *   
    FROM #ALLROW  
    WHERE RowID  > @LowerBand AND RowID < @UpperBand  
END 

CREATE PROCEDURE GetEmployeePaged
     @PageSize int = 10,-- pagesize
    @CurrentPage int = 1,-- current page no
    @ItemCount int output -- total employee found
AS
BEGIN
    -- declare local variables for pagination
    DECLARE @UpperBand INT,
            @LowerBand INT
 SET @LowerBand = (@CurrentPage - 1)* @PageSize
    SET @UpperBand = @CurrentPage* @PageSize + 1
   
    -- assign itemcount output parameter
    SET @ItemCount = (
        SELECT COUNT(employeeId)
        FROM employees
    )

    -- create temporary table to store paged data
    CREATE TABLE #ALLROW(
           RowID INT PRIMAY KEY IDENTITY(1,1),
            EmployeeId INT,
            Name VARCHAR(255),
            salary DECIMAL(7,2)
        )

    -- insert data into the temporary table
    INSERT INTO #ALLROW
    SELECT EmployeeId, Name, salary
    FROM employees

    -- get paged data
 SELECT *
    FROM #ALLROW
    WHERE RowID  > @LowerBand AND RowID < @UpperBand
END
First in parameter list we have three parameters and their meanings are exactly what they are. @pagesize specifies number of record per page, @currentpage specifies the current page number and @itemcount specifies total record found. So we can get the employee record in the page 1 with 10 record per page by calling :

view plaincopy to clipboardprint?
EXEC GetEmployeePaged(10,1,@itemcount) 

EXEC GetEmployeePaged(10,1,@itemcount)
The next we declare two local variables. These variables is used inside the stored procedure for determining the start row and end row we will retrive the records. These variables' values are calculated based on the @pagesize and @currentpage.

Then we use SET statement to assign output parameter @itemcount to the total records of the employee.

Finally we create a temporary table to store the data, insert the data into the temporary table and retrieve the need records.

As you can see the stored procedure is very flexible, you can leverage it to deal with tough situation in database developement.

The next statement we will show you how to use local variable and use SET statement as shown in the example above in the stored procedure. Next tutorial: Local variable in stored procedure














Local Variables
Local variables are used in stored procedure to keep temporary intermediate results. In order to use a local variable we have to declare it explicitly as follows:

view plaincopy to clipboardprint?
<declare variable statement> ::=  
   DECLARE <variable list> <data type> [  
      DEFAULT <expression> ]  
 
<variable list> ::=  
   <variable> [ { , <variable> }... ] 

<declare variable statement> ::=
   DECLARE <variable list> <data type> [
      DEFAULT <expression> ]

<variable list> ::=
   <variable> [ { , <variable> }... ]
DECLARE keywords is used to declare local variables. This source code snippet show you how to declare a numeric and an alphanumeric variable:

view plaincopy to clipboardprint?
DECLARE @Found INT 
DECLARE @Firstname VARCHAR(255) 

DECLARE @Found INT
DECLARE @Firstname VARCHAR(255)
Some database server support initial value for local variables and some not. If supported, we can specify the default value for local variable. The expression for the default value of local variables are not limited to literals (like 'name' or 1) but may consist of compound expressions, including scalar subqueries.

Local variable has its own scope. It does not exist when the stored procedure finish. Inside each code block local variable only visible it the block it declared.

The SET statement can be used to assign value to local variable. Here is the syntax :

view plaincopy to clipboardprint?
<set statement> ::=  
   SET <local variable definition>  
       [ {, <local variable definition> }... ]  
 
<local variable definition> ::=  
   <local variable> { = | := } <scalar expression> 

<set statement> ::=
   SET <local variable definition>
       [ {, <local variable definition> }... ]

<local variable definition> ::=
   <local variable> { = | := } <scalar expression>

We can use SET statement to assign value or expression to local variable, you can refer to the example in stored procedure body tutorial.

Local variable is intermedia storage to store temporary result in stored procedure.In the next tutorial, We will show you how to use flow-control statements in stored procedure.











Common Usage Flow-Control in Stored Procedure
In this section, we will cover all basic flow-control statement which can be used in stored procedure. There are two most common usage of flow-control which are conditional execution and loop.

Conditional execution with IF-THEN-ELSE and CASE statement
The IF-THEN-ELSE statement is used to evaluate the value of boolean expression; if the value is True it execute the block code that follows it otherwise it will execute the block code that follows by ELSE.The ELSE part is optional in the statement.

Here is an stored procedure which finds the maximum value between two integers using IF-THEN-ELSE statement. The example is easy just for demonstration.

view plaincopy to clipboardprint?
CREATE PROCEDURE FindMax  
    @v1 INT,  
    @v2 INT,  
    @m INT OUTPUT 
AS 
BEGIN 
    IF @v1 > @v2  
        SET @m = @v1  
    ELSE   
        SET @m = @v2  
END 

CREATE PROCEDURE FindMax
    @v1 INT,
    @v2 INT,
    @m INT OUTPUT
AS
BEGIN
    IF @v1 > @v2
        SET @m = @v1
    ELSE
        SET @m = @v2
END
In complex cases, we can use CASE statement instead of IF-THEN-ELSE statement. CASE statement evaluates a list of boolean expression and returns one of multiple possible result expressions. CASE statement is similar to the swith-case statement in modern programming language such as C# or Java. Here is the example of using CASE statement to display salary level of employee. We have employee table

employee_id  name      salary
-----------  --------  -------
          1  jack      3000.00
          2  mary      2500.00
          3  newcomer  2000.00
          4  anna      2800.00
          5  Tom       2700.00
          6  foo       4700.00
And here is the example

view plaincopy to clipboardprint?
CREATE PROCEDURE DisplaySalaryLevel  
AS   
BEGIN 
    SELECT  employeeId, name,salary, salary_level =  
    CASE salary  
        WHEN salary < 1000   
            THEN 'very low' 
        WHEN salary > 1000 AND salary < 2000   
            THEN 'low' 
        WHEN salary > 2000 AND salary < 4000   
            THEN 'average' 
        WHEN salary > 4000 AND salary < 10000   
            THEN 'high' 
        WHEN salary > 10000   
            THEN 'very high' 
    END 
    FROM Production.Product  
END 

CREATE PROCEDURE DisplaySalaryLevel
AS
BEGIN
    SELECT  employeeId, name,salary, salary_level =
    CASE salary
        WHEN salary < 1000
            THEN 'very low'
        WHEN salary > 1000 AND salary < 2000
            THEN 'low'
        WHEN salary > 2000 AND salary < 4000
            THEN 'average'
        WHEN salary > 4000 AND salary < 10000
            THEN 'high'
        WHEN salary > 10000
            THEN 'very high'
    END
    FROM Production.Product
END
Looping with WHILE statement
Since T-SQL is fourth generation language and designed to operate with sets of data so it is also possible to write the code to loop through the record set and perform operations on a single record. It will cause the performance of the server but in some cases it is necessary to use it. Here we will you an example of using WHILE loop statement to calculate the factorial of an integer number just for demonstration.

view plaincopy to clipboardprint?
CREATE PROCEDURE Cal_Factorial  
  @inyN INT,  
  @intFactorial BIGINT OUTPUT 
AS 
BEGIN     
    SET @intFactorial = 1  
 
    WHILE @inyN > 1  
    BEGIN 
        SET @intFactorial = @intFactorial * @inyN  
        SET @inyN = @inyN - 1  
    END 
END 

CREATE PROCEDURE Cal_Factorial
  @inyN INT,
  @intFactorial BIGINT OUTPUT
AS
BEGIN    
    SET @intFactorial = 1

    WHILE @inyN > 1
    BEGIN
        SET @intFactorial = @intFactorial * @inyN
        SET @inyN = @inyN - 1
    END
END
In the real world database programming you will need to use the conditional statement to make you stored procedure. Next we will show you how to modify stored procedure using ALTER and DROP statement




Modifying and Compiling Stored Procecdure
Once a stored procedure is resided in the database server catalog, we can modify it by using the ALTER PROCEDURE statement as follows:

view plaincopy to clipboardprint?
ALTER PROCEDURE spName  
    (parameter_list)  
AS 
BEGIN 
    -- stored procedure body here  
END 

    ALTER PROCEDURE spName
        (parameter_list)
    AS
    BEGIN
        -- stored procedure body here
    END
To remove a stored procedure from database catalog, we can use DROP PROCEDURE

view plaincopy to clipboardprint?
DROP PROCEDURE spName 

    DROP PROCEDURE spName
When you remove stored procedure, be noted that some database products have a feature that allows you to remove the dependency database objects like table, view, index or other stored procedures also.

Stored procedure is compiled before it executes. Each database server has its own compiling strategy. You can specify the compiling option when you with WITH RECOMPILE statement as follows:

view plaincopy to clipboardprint?
CREATE PROCEDURE spName  
   (parameter_list) AS 
   WITH RECOMPILE  
BEGIN 
    -- stored procedure body here  
END 

CREATE PROCEDURE spName
   (parameter_list) AS
   WITH RECOMPILE
BEGIN
    -- stored procedure body here
END
The WITH RECOMPILE guarantee that each time the stored procedure is invoked, the compiler is called and compile the stored procedure again. With WITH RECOMPILER the stored procedure is recompile and adjusted to the current situation of the database which brings some advantages to the processing strategy. But the compilation takes time and also decrease the performance. Therefore for each stored procedure we can specify which is the best to use WITH RECOMPILE.