Stored Procedures

Imagine that you create a database that includes employees. When you want to perform payroll for an employee, you would need his or her weekly hours and his or her hourly salary. To calculate the weekly salary, you would write an equation such as:

Weekly Salary = Weekly Hours * Hourly Salary

Since there are various employees in the company, you would need a fast means of performing this kind of assignment, maybe automatically, for each employee. This is the basis of a function. Referred to as a function or a routine in most other programming languages, a procedure is an (or a relatively small) assignment that can take care of a task in a database so that you can call it as/when needed to get its result.

Creating a Procedure

A procedure can be as simple as calculating 124 + 68 or as complex as finding out if a date range includes a holiday for somebody who is renting a car so the day can be calculated with a different rate as compared to other dates in the same range. As always, we should start with simple examples. To create a procedure, you can use either the Enterprise Manager or the SQL Query Analyzer.

To create a new procedure in the Enterprise Manager, after expanding the database, you can right-click it, position the mouse on New, and click Stored Procedure... You would be presented with a skeleton syntax that you can complete using the techniques we will learn in this lesson.

To create a new procedure in the SQL Query Analyzer, after selecting the database (either from the combo box on the toolbar or with the USE keyword), you can type code based on the syntaxes we will learn shortly.

The creation of a procedure starts with the CREATE PROCEDURE expression. You can also use CREATE PROC. Both expressions produce the same result.

Like everything in your database, you must name your procedure. The name of a procedure can be any string that follows the rules we reviewed for naming objects. There are some other rules or suggestions you should or must follow when naming your procedure. For example, refrain from starting the name of a procedure with sp_ because it would conflict with some of the procedures that already ship with SQL Server.

After the name of the procedure, type the keyword AS.

The section, group of words, or group of lines after the AS keyword is called the body of the procedure. It states what you want the procedure to do or what you want it to produce.

Based on this, the simplest syntax of defining a procedure is:

CREATE PROCEDURE ProcedureName
AS
Body of the Procedure

It is important to keep in mind that there are many other issues related to creating a procedure but for now, let's consider that syntax.

Executing a Procedure

After creating a procedure, to get its result, you would need to execute it (in other programming languages, we would say that, in order to use a function, you must call it). To execute a procedure, you use the EXECUTE keyword followed by the name of the procedure. Although there are some other issues related to executing a procedure, for now, we will consider that the simplest syntax to call a procedure is:

EXECUTE ProcedureName

Alternatively, instead of EXECUTE, you can use the EXEC keyword:

EXEC ProcedureName

After a procedure has been executed, it is saved using its name. Since it becomes stored as an integral part of the database, a SQL procedure is also called a Stored Procedure.

Exploring Procedures

 

Introduction

Probably the simplest procedure you can write would consist of selecting columns from a table. This is done with the SELECT keyword and applying the techniques we reviewed for data analysis. For example, to create a procedure whose job would consist of creating a list of car makes from a table called Cars, you can write:

CREATE PROCEDURE ListOfMakes
AS
SELECT Make

To execute this procedure, you would type:

EXECUTE ListOfMakes

You can also create a procedure that selects more than one column from a table. As done with the SELECT keyword in data analysis, you would separate each item of the list with a comma, except for the last. Here is an example:

CREATE PROCEDURE ListOfCars
AS
SELECT Make, Model, CarYear

One of the advantages of using procedures is that not only can they produce the same expressions as we saw during analysis but also they can store such expressions to be recalled any time without having to re-write them. Based on this, you can create an expression that combines a first and a last name to produce and store a full name. Here is an example:

CREATE PROC GetFullName
AS
SELECT FullName = FirstName + ', ' + LastName
FROM Students

Imagine you are creating an application for a department store that sometimes applies discounts of 10%, 20%, 40%, 55%, 70%, etc on items it sells. Since the management decides when and what discount would be applied on an item, you cannot predict all possibilities. One way to solve this type of problem is to create a procedure that would receive the discount applied on an item and then apply this discount to the price of the item.

All of the procedures we have created and used so far assumed that the values they needed were already in a table of the database. In some cases, you may need to create a procedure that involves values that are not part of the database. On such a scenario, for the procedure to carry its assignment, you would supply it with one or more values. 

An external value that is provided to a stored procedure is called a parameter. When you create a procedure, you must also create the parameter if you judge it necessary. When a procedure's creation is equipped with a parameter, it is said that the procedure takes an argument. A procedure can also take more than one argument.

When you execute a procedure that takes one or more arguments, you must provide a value for each argument. In this case, you are said to pass a value for the argument. There are cases when you don't have to provide an argument. We will learn how this is done.

Passing Arguments

To create a procedure that takes an argument, type the formula CREATE PROCEDURE or CREATE PROC followed by the name of the procedure, then type the name of the argument that starts with @. The parameter is created like a column of a table. That is, a parameter must have a name, a data type and an optional length. Here is the syntax you would use:

CREATE PROCEDURE ProcedureName
@ParameterName DataType
AS
Body of the Procedure

When implementing the procedure, you can define what you want to do with the parameter(s), in the body of the procedure. One way you can use a parameter is to run a query whose factor the user would provide. For example, imagine you want to create a procedure that, whenever executed, it would be supplied with a gender, then it would display the list of students of that gender. Since you want the user to specify the gender of students to display, you can create a procedure that receives the gender. Here is an example:

CREATE PROC GetListOfStudentsByGender
@Gdr VARCHAR(12)
AS
SELECT FirstName, LastName, DateOfBirth, HomeOfBirth, HomePhone, Gender
FROM Students
WHERE Gender = @Gdr

As mentioned already, when executing a procedure that takes a parameter, make sure you provide a value for the parameter. The syntax used is:

EXEC ProcedureName ParameterValue

If the parameter is Boolean or numeric, make sure you provide an appropriate value. If the parameter is a character or a string, type its value in single-quotes. Here is an example:

Notice that we could/should have omitted to include the Gender column in the statement since it would be implied to the user.

Another type of procedure can be made to take more than one parameter. In this case, create the parameters in the section before the AS keyword, separated by a comma. The syntax you would use is:

CREATE PROCEDURE ProcedureName
@ParameterName1 DataType, @ParameterName2 DataType, @ParameterName_n DataType
AS
Body of the Procedure

When calling a procedure that takes more than one parameter, you must still provide a value for each parameter but you have two alternatives. The simplest technique consists of providing a value for each parameter in the exact order they appear in the procedure.

Alternatively, you can provide the value for each parameter in the order of your choice. In this case, you must type the name of each parameter and assign it the corresponding value.

Imagine you create a table for a department store and the table would be used to hold the names and prices of items (in this example, the table is called SaleItems):

Supposed you have filled the table with a few items as follows:

Imagine you want to create a mechanism of calculating the price of an item after a discount has been applied to it. Such a procedure can be created as follows:

CREATE PROC CalculateNetPrice
@discount Decimal
AS
SELECT ItemName, ItemPrice - (ItemPrice * @discount / 100)
FROM SaleItems

This can be executed as follows:

If you are planning to create a procedure that takes an argument and know that the argument will likely have the same value most of the time, you can provide that value as parameter but leave a room for other values of that argument. A value given to an argument is referred to as default. What this implies is that, when the user calls that stored procedure, if the user doesn't provide a value for the argument, the default value would be used.

To create a procedure that takes an argument that carries a default value, after declaring the value, on its right side, type = followed by the desired value. Here is an example applied to the above database:

CREATE PROC CalculateNetPrice
@discount Decimal = 20.00
AS
SELECT ItemName, ItemPrice - (ItemPrice * @discount / 100)
FROM SaleItems

When executing a procedure that takes a default argument, you don't have to provide a value for the argument if the default value suits you. Based on this, the above procedure can be called as follows:

If the default value doesn't apply to your current calculation, you can provide a value for the argument. Here is an example:

Using this same approach, you can create a procedure that takes more than one argument with default values. To provide a default value for each argument, after declaring it, type the desired value to its right side. Here is an example of a procedure that takes two arguments, each with a default value:

When calling a procedure that takes more than one argument and all arguments having default values, you don't need to provide a value for each argument, you can provide a value for only one or some of the arguments. The above procedure can be called with one argument as follows:

EXEC CalculateSalePrice2 55.00

In this case, the other argument(s) would use their default value.

We saw that, when calling a procedure that takes more than one argument, you didn't have to provide the values of the argument in the exact order they appear in the procedure, you just had to type the name of each argument and assign it the corresponding value. In the same way, if a procedure takes more than one argument and some of the arguments have default values, when calling it, you can provide the values in the order of your choice, by typing the name of each argument and assigning it the desired value. Based on this, the above procedure can be called with only the value of the second argument as follows:

EXEC CalculateSalePrice2 @TaxRate = 8.55

In this case, the first argument would use its default value.

Output Parameter

Many languages use the notion of passing an argument by reference. This type of argument is passed to a procedure but it is meant to return a value. Transact-SQL uses the same technique. In other words, you can create a procedure that takes a parameter but the purpose of the parameter is to carry a new value when the procedure ends so you can use that value as you see fit.

To create a parameter that will return a value from the procedure, type the OUTPUT keyword on the right side of the parameter. A syntax you can use is:

CREATE PROCEDURE ProcedureName
@ParameterName DataType OUTPUT
AS
Body of the Procedure

You can also create a procedure that takes a mix of value and output parameters.

Managing Procedures

Deleting a Procedure

One of the biggest characteristics of a stored procedure, as compared to functions in traditional languages, is that a procedure is treated like an object in its own right. Therefore, after creating it, if you don't need it anymore, you can get rid of it.

There are various types of procedures, some of which are considered temporary. Those types of procedures delete themselves when not needed anymore, such as when the person who created the procedure disconnects from the database or shuts down the computer. Otherwise, to delete a procedure, you can use either Enterprise Manager or SQL Query Analyzer. As mentioned with tables, even if you create a procedure in Enterprise Manager, you can delete it using SQL Query Analyzer and vice-versa.

To remove a procedure in Enterprise Manager, after expanding the database, click the Stored Procedure node. If you see the needed procedure in the list, fine. If you don't see the procedure in the list but know for sure that the procedure was created, for example if you create a new procedure in SQL Query Analyzer, it doesn't automatically appear in Enterprise Manager, make sure you refresh the list first by pressing F5. After locating the procedure in the right frame, you can either right-click it and click Delete, or click it to select it and then press Delete. Before the procedure gets removed, you would be warned with a dialog box to make your final decision.

To delete a procedure in SQL Query Analyzer using Transact-SQL, the syntax to use is:

DROP PROCEDURE ProcedureName

Of course, you should make sure you are in the right database and also that the ProcedureName exists.