Home

Stored Procedures

 

Fundamentals of Stored Procedures

 

Introduction

We have had an introduction to Transact-SQL functions. The SQL provides another type of action called a stored procedure. Like a function, a stored procedure is used to perform an action on a a database.

Visually Creating a Stored Procedure

To create a procedure:

  • In Microsoft SQL Server Management Studio:
    • In the Object Explorer, expand the database for which you want to create the procedure, expand its Programmability node, right-click Stored Procedures, and click New Stored Procedure... A query window with a skeleton syntax would be displayed. You can then modify that code using the techniques we will learn in this lesson
    • Open an empty query window associated with the database for which you want to create the stored procedure and display the Templates Explorer. In the Template Explorer, expand the Store Procedure node. Drag Create Stored Procedure and drop it in the query window
    • Open an empty query window associated with the database for which you want to create the stored procedure and enter the necessary code
  • In Microsoft Visual Studio, in the Server Explorer, under the database connection, right-click Stored Procedure and click Add New Stored Procedure. An empty window will open in the Code Editor, waiting for you to do your thing
 

Programmatically Creating a Stored Procedure

In SQL, to create a stored procedure, you start 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 the functions
  • Refrain from starting the name of a procedure with sp_ because it may conflict with some of the stored procedures that already ship with Microsoft 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 creating a procedure is:

CREATE PROCEDURE ProcedureName
AS
Body of the Procedure

You can also start the body of the stored procedure with BEGIN and end it with END. The formula to use would be:

CREATE PROCEDURE ProcedureName
AS
BEGIN
Body of the Procedure
END

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

After creating the procedure, you must store it as an object in your database. To do this:

  1. If you are working in Microsoft SQL Server Management Studio, on the SQL Editor toolbar, you can click the Execute button. If the code of the procedure is right, a new node and a name for the stored procedure would be added to the Stored Procedures section of the database
  2. In you are working in Microsoft Visual Studio, first save the stored procedure. If it is already closed, open it from the Server Explorer. Then, you can right-click the Code Editor and click Execute. The result would show in the Output window

Managing Stored Procedures

 

Modifying a Procedure

As a regular SQL Server database object, you can modify a stored procedure without recreating it. To do this:

  • In Microsoft SQL Server Management Studio:
    • In the Object Explorer, you can right-click the procedure and click Modify
    • In the Object Explorer, you can right-click the procedure, position the mouse on Script Stored Procedure As -> ALTER To -> New Query Editor Window
    • Open an empty query window associated with the database that contains the stored procedure. From the Template Explorer, expand Stored Procedure. Drag the Drop Stored Procedure node and drop it in the empty query window
  • In Microsoft Visual Studio, in the Server Explorer, under the database connection, under the Stored Procedures node, double-click the stored procedure or right-click it and click Open

In each case, the code of the stored procedure would open and you can modify it as you see fit. After editing the code, you can execute the SQL statement to update the stored procedure.

In SQL, the basic formula to modify a stored procedure is:

CREATE PROCEDURE ProcedureName
AS
BEGIN
    Body of the Procedure
END

Deleting a Procedure

One of the biggest characteristics of a stored procedure is that it is treated like an object in its own right. Therefore, after creating it, if you do not need it anymore, you can get rid of it.

There are various types of stored 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 the Object Explorer of Microsoft SQL Server Management Studio of the Server Explorer of Microsoft Visual Studio. As mentioned with tables, even if you create a procedure using the Object Explorer or the Server Explorer, you can delete it using SQL code.

To delete a stored procedure:

  • In the Object Explorer of Microsoft SQL Server Management Studio, after expanding the database, its Programmability, and its Stored Procedure nodes, you can right-click the procedure and click Delete. You can also click it in the Object Explorer to select it and then press Delete. The Delete Object dialog box would come up to let you make a decision
  • In the Server Explorer of Microsoft Visual Studio, you can right-click the stored procedure and click Delete. A message box would warn and allow you to confirm your decision or change your mind

To delete a procedure in 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.

Exploring Procedures

 

Introduction

Probably the simplest procedure you can write would consist of selecting columns from a table. To do this in Microsoft Visual Studio, in the Code Editor, you can right-click somewhere after the AS operator and click Insert SQL:

Stored Procedure

This action would display the Query Builder. From there, you can select the tables of the database and the desired columns of the table(s). You can then build a SQL expression as you see fit. After building it, you can click OK. A SQL SELECT expression would be generated for you.

To manually create a SQL expression, you can enter a SELECT expression after AS and apply the techniques we reviewed for data analysis. For example, to create a stored procedure that would hold a list of students from a table named Students, you would create the procedure as follows:

CREATE PROCEDURE GetStudentIdentification
AS
BEGIN
    SELECT FirstName, LastName, DateOfBirth, Sex
    FROM Students
}
GO

Besides SELECT operations, in a stored procedure, you can perform any of the database operations we have applied so far. These include creating and maintaining records, etc.

Executing a Procedure

To get the results of creating a procedure, you must execute it (in other words, to use a stored procedure, you must call it). To execute a stored procedure in Microsoft Visual Studio:

  • If the stored procedure is opened in the Code Editor, you can right-click anywhere in the window and click Execute
  • In the Server Explorer (even if the stored procedure is displaying its text in the Code Editor), you can right-click the name of the stored procedure and click Execute

To execute a procedure in SQL, 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

For example, if you have a stored procedure named GetStudentIdentification, to execute it, you would type:

EXECUTE GetStudentIdentification

You can also precede the name of the procedure with its schema, such as dbo. Here is an example:

EXECUTE dbo.GetStudentIdentification;

You can also precede the name of the schema with the name of the database. Here is an example:

EXECUTE ROSH.dbo.GetStudentIdentification;

Using Expressions and Functions

One of the advantages of using stored procedures is that not only can they produce the same expressions as we saw during data 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 PROCEDURE GetStudentIdentification
AS
BEGIN
    SELECT FullName = FirstName + ' ' + LastName,
           DateOfBirth, Sex
    FROM Students
END

A stored procedure can also call a function in its body. To do this, follow the same rules we reviewed for calling functions during data analysis. Here is an example of a stored procedure that calls a function:

USE ROSH;
GO
CREATE PROCEDURE GetStudentsAges
AS
BEGIN
    SELECT FullName = FirstName + ' ' + LastName,
           DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
           Sex
    FROM Students
END

Here is an example of executing the procedure:

The Output Window

 

Introduction to Arguments of a Stored Procedure

 

Introduction

Like a normal Visual Basic procedure and like a function in Transact-SQL, a store procedure can take 0, 1, 2, or more arguments. An argument allows a procedure to receive values from the code that is accessing the procedure. For example, if you decide to calculate the age of a student, because there are many students, you can create a stored procedure that receives a student's date of birth, the procedure can then use that value and it would produce a number that represents the corresponding age.

All of the procedures we used in the previous sections of this lesson assumed that the values they needed were already in a table of the database. In some cases, you may need to create a stored 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. 

When it comes to arguments, most, if not all, of the rules used in the procedures of a Visual Basic application also apply to a procedure. For example, when you execute a procedure that takes one or more arguments, you must provide a value for each argument.

Passing Arguments

To create a stored 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 an argument starting with the @ symbol. 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 depending on the type. Here is the syntax you would use:

CREATE PROCEDURE ProcedureName
@ParameterName DataType
AS
BEGIN
    Body of the Procedure
END

When implementing the stored 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, would be supplied with a student's gender, then it would display the list of students of that gender. Since you want the user to specify the sex of students to display, you can create a procedure that receives the sex. Here is an example:

CREATE PROC dbo.GetListOfStudentsBySex
       @Gender VARCHAR(12)
AS
SELECT FirstName, LastName,
       DateOfBirth, HomePhone, Sex
FROM   Students
WHERE  Sex = @Gender

Executing an Argumentative Stored Procedure

 

In Microsoft Visual Studio

As seen in previous sections, to call a stored procedure in Microsoft Visual Studio, you can right-click it in the Server Explorer and click Execute. If the procedure takes at least one argument, you must supply it. If you are working visually, a dialog box would come up to allow you to provide a value for the argument. Here is an example after right-clicking the above store procedure and clicking Execute:

Running a Stored Procedure

As you can see, you can enter the desired value in the Value column, then click OK. The result would then appear in the Output window:

The Output Window

In the same way, if you execute a stored procedure that takes more than one argument, in the Run Stored Procedure dialog box, specify the desired but right value of each argument. The value must be of the appropriate type.

In SQL

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 numeric value. If the parameter is a character or a string, type its value in single-quotes. Here is an example:

EXEC ROSH.dbo.GetListOfStudentsBySex 'Male';

Here is an example of executing it:

Executing a Stored Procedure

Notice that we could/should have omitted to include the Sex 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. Separate the parameters by a comma. The syntax you would use is:

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

Here is an example:

CREATE PROCEDURE IdentifyStudentsByState
	@Gdr varchar(20),
	@StateOrProvince char(2)
AS
BEGIN
    SELECT FullName = LastName + ', ' + FirstName,
           DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
           Sex
    FROM Students
    WHERE (Sex = @Sx) AND (State = @StateOrProvince)
END

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. Here is an example:

USE ROSH;
GO
EXEC ROSH.dbo.IdentifyStudentsByState 'Female', 'MD';
GO

This would produce:

Executing a Stored Procedures That Takes Many Arguments

Alternatively, you can provide the value for each parameter in the order of your choice. Consider the following procedure that takes 3 arguments:

CREATE PROCEDURE IdentifySomeStudents
    @Sx varchar(20),
    @StateOrProvince char(2),
    @HomeStatus bit
AS
BEGIN
    SELECT FullName = LastName + ', ' + FirstName,
           DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
           Sex
    FROM Students
    WHERE (Sex = @Sx) AND 
          (State = @StateOrProvince) AND
          (SPHome = @HomeStatus)
END

If you visually execute this type of procedure, in the Run Stored Procedure dialog box, enter the desired but appropriate value under the Value column corresponding to each argument. Here is an example:

Running a Stored Procedure With Various Arguments

After clicking OK, this would produce:

The Output Window

If you are programmatically executing this type of procedure, you can type the name of each parameter and assign it the corresponding value. Here is an example:

EXEC IdentifySomeStudents @HomeStatus=1, @StateOrProvince='MD', @Sx='Female';

Stored Procedure

Default Arguments

Imagine you create a database for a department store and a table that holds the list of items sold in the store:

Department Store: Item Categories
 
Department Store: Store Items

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

Department Store: Item Sizes

ItemNumber ItemCategoryID ItemName ItemSize UnitPrice
264850 2 Long-Sleeve Jersey Dress Petite 39.95
930405 4 Solid Crewneck Tee Medium 12.95
293004 1 Cotton Comfort Open Bottom Pant XLarge 17.85
924515 1 Hooded Full-Zip Sweatshirt S 69.95
405945 3 Plaid Pinpoint Dress Shirt 22 35-36 35.85
294936 2 Cool-Dry Soft Cup Bra 36D 15.55
294545 2 Ladies Hooded Sweatshirt Medium 45.75
820465 2 Cotton Knit Blazer M 295.95
294694 2 Denim Blazer - Natural Brown Large 75.85
924094 3 Texture-Striped Pleated Dress Pants 44x30 32.85
359405 3 Iron-Free Pleated Khaki Pants 32x32 39.95
192004 3 Sunglasses   15.85

Department Store: Store Items

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 PROCEDURE dbo.CalculateNetPrice 
    @discount Decimal
AS
    SELECT ItemName, UnitPrice - (UnitPrice * @discount / 100)
    FROM StoreItems

This can be executed as follows:

Running a Stored Procedure

Output

If you are planning to create a stored procedure that takes an argument and you know that the argument will likely have the same value most of the time, you can provide that value as a 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 does not provide a value for the argument, the SQL interpreter would use the default value.

To create a stored 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 PROCEDURE dbo.CalculateDiscountedPrice 
    @discount decimal = 10.00
AS
    SELECT ItemName, UnitPrice - (UnitPrice * @discount / 100)
    FROM StoreItems;

When executing a stored procedure that takes a default argument, you do not have to provide a value for the argument if the default value suits you. If you are executing the procedure in Microsoft Visual Studio, when the Run Stored Procedure dialog box comes up, you can leave the Value with the <DEFAULT> option:

Running a Stored Procedure With a Default Argument

Based on this, the above procedure call would produce:

Output

If the default value does not apply to your current calculation, you can provide a value for the argument.

You can create a stored 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 stored procedure that takes two arguments, each with a default value:

CREATE PROCEDURE dbo.CalculateSalePrice 
    @Discount Decimal = 20.00,
    @TaxRate Decimal = 7.75
AS
    SELECT ItemName As [Item Description],
    UnitPrice As [Marked Price],
    UnitPrice * @Discount / 100 As [Discount Amt],
    UnitPrice - (UnitPrice * @Discount / 100) As [After Discount],
    UnitPrice * @TaxRate / 100 As [Tax Amount],
    (UnitPrice * @TaxRate / 100) + UnitPrice - 
    (UnitPrice * @Discount / 100) + (@TaxRate / 100) As [Net Price]
    FROM StoreItems;
RETURN

Here is an example of executing the procedure:

Running a Stored Procedure

Output

When calling a stored procedure that takes more than one argument with all arguments having default values, you do not 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 its(their) default value(s). We saw that, when calling a procedure that takes more than one argument, you did not have to provide the values of the argument in the exact order they appeared in the procedure, you just had to type the name of each argument and assign it the desired 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 Parameters

Many computer language use the notion of passing an argument by reference. This type of argument is passed to a stored procedure but it is meant to return a value. Transact-SQL uses the same technique. In other words, you can create a stored 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 a stored procedure, after the name of the procedure, if you want the procedure to take arguments, type them. Otherwise, omit them. On the other hand, you must pass at least one argument, name it starting with the @ symbol, specify its data type, and enter the OUTPUT keyword on its right. Based on this, the basic syntax you can use is:

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

or

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

In the body of the stored procedure, you can perform the assignment as you see fit. The primary rule you must follow is that, before the end of the procedure, you must have specified a value for the OUTPUT argument, that is, the value that the procedure will return. Here is an example:

CREATE PROCEDURE dbo.CreateFullName
    @FName varchar(20),
    @LName varchar(20), 
    @FullName varchar(42) OUTPUT
AS
    SELECT @FullName = @LName + ', ' + @FName

When calling the procedure, you must pass an argument for the OUTPUT parameter and, once again, you must type OUTPUT to the right side of the argument. Remember that the procedure would return the argument. This means that, after calling the procedure, you can get back the OUTPUT argument and use it as you see fit. Here is an example:

DECLARE @FirstName varchar(20),
	@LastName varchar(20),
        @Full varchar(42)
SET @FirstName = 'Melanie';
SET @LastName = 'Johanssen';

EXECUTE dbo.CreateFullName @FirstName, @LastName, @Full OUTPUT

SELECT @Full;

One of the advantages of using a function or a stored procedure is that it has access to the tables and records of its database. This means that you can access the columns and records as long as you specify the table or the view, which is done with a FROM clause associated with a SELECT statement. Consider the following stored procedure created in a database that contains a table named Students:

USE ROSH;
GO

CREATE PROCEDURE ShowStudentsFullNames
    @FullName varchar(42) OUTPUT
AS
    SELECT @FullName = LastName + ', ' + FirstName FROM Students;
GO

When you execute this procedure, it would work on the records of the table. One of the particularities of a procedure that takes an OUTPUT argument is that it can return only one value. Consider the following example of executing the above procedure:

When calling such a procedure, if you do not specify a condition to produce one particular result, the SQL interpreter in this case would select the last record. This means that you should always make sure that your procedure that takes an OUTPUT parameter would have a way to isolate a result. If the procedure processes a SELECT statement, you can use a WHERE condition. Here is an example of such a procedure:

USE ROSH;
GO

CREATE PROCEDURE ShowStudentsFullNames
    @FullName varchar(42) OUTPUT
AS
    SELECT @FullName = LastName + ', ' + FirstName FROM Students
    WHERE StudentID = 8;
GO

When this procedure is executed, it would produce only the record stored in the 8th position of the table.

 

Home Copyright © 2008-2012 FunctionX