In Lesson 6, we had an
introduction to some types of actions that could be performed on a
database. These actions were called functions. The SQL provides another
type of action called a stored procedure. If you have developed
applications in some other languages such as Pascal or Visual Basic, you
are probably familiar with the idea of a procedure. Like a function, a
stored procedure is used to perform an action on a database.

In the Object Explorer, expand the Databases node if necessary and expand WattsALoan

Click Database Diagram

When the message box comes up, read it and click Yes

Right-click Database Diagram and click New Database Diagram...

In the dialog box, double-click each table and, when all tables have been
added, click Close

Save the diagram as dgmWattsALoan and close it

Creating a Stored Procedure

To create a procedure:

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
Templates 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 SQL, to create a 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:

Refrain from starting the name of a procedure with sp_ because it
could 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

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, on the SQL Editor toolbar,
you can click the Execute button . If the code of the procedure is right, it
would be created and a new node for its name would be added to the Stored
Procedures section of the database.

Managing Procedures

Modifying a Procedure

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

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 Templates Explorer, expand Stored Procedure. Drag
the Drop Stored Procedure node and drop it in the empty query window

In each case, a skeleton code would be generated for you.
You can then edit it to create a new version of your stored procedure. 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:

ALTER PROCEDURE ProcedureName
AS
Body of Procedure

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
don't 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 stored procedure
disconnects from the database or shuts down the computer. Otherwise, to
delete a procedure, you can use either the Object Explorer or SQL. As mentioned with tables, even if you create a procedure
using the Object Explorer, you can delete it using SQL and
vice-versa.

To remove a procedure in the Object Explorer, after
expanding its database, its Programmability, and its Stored Procedure
nodes, you can right-click the stored 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.

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. This is done with the SELECT
operator and applying 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:

To get the results of creating a stored procedure, you
must execute it (in other words, to use a stored procedure, 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

For example, if you have a 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;

Practical Learning: Executing a Stored Procedure

Delete the contents of the query window and replace it with the following:

EXECUTE AssignDefaultPassword;
GO

To execute the stored procedure, press F5

Using Expressions and Functions

One of the advantages of using a stored procedure is that not
only can it produce the same expression as we saw during analysis but
also it can store such an expression to be recalled any time without
having to re-write it (the expression). 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:

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:

Delete the contents of the query window and replace it with the following:

EXECUTE CreateUsername;
GO

To execute the stored procedure, press F5

Arguments and Parameters

Introduction

All of the stored 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 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.

An external value that is provided to a stored
procedure is called a parameter. When you create a stored 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
stored procedure takes an argument. A stored procedure can also take more than one
argument.

When you execute a stored 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.

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 the argument starting 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 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 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 stored procedure that
receives the gender. Here is an example:

Delete the contents of the query window and, to pass arguments to a stored procedure,
type the following in the window:

USE WattsALoan;
GO
CREATE PROCEDURE SpecifyCurrentBalance
@PmtDate datetime,
@EmplID int,
@LaID int,
@PmtAmt money
AS
BEGIN
-- Get the amount that was lent to the customer
DECLARE @AmountOfLoan money;
SET @AmountOfLoan = (SELECT las.FutureValue
FROM LoanAllocations las
WHERE (las.LoanAllocationID = @LaID));
-- If the customer had already made at least one payment,
-- get the current balance of the customer's account
DECLARE @CurrentBalance money;
SET @CurrentBalance = (SELECT MIN(pay.Balance)
FROM Payments pay
WHERE (pay.LoanAllocationID = @LaID));
-- If the customer has never made a payment (yet),
-- to specify the balance, subtract the current payment
-- from the original amount of the loan
IF @CurrentBalance IS NULL
BEGIN
INSERT INTO Payments(PaymentDate, EmployeeID,
LoanAllocationID, PaymentAmount, Balance)
VALUES(@PmtDate, @EmplID, @LaID, @PmtAmt,
@AmountOfLoan - @PmtAmt);
END
-- If the customer had already at least one payment,
-- subtract the current payment from the previous balance
ELSE
BEGIN
INSERT INTO Payments(PaymentDate, EmployeeID,
LoanAllocationID, PaymentAmount, Balance)
VALUES(@PmtDate, @EmplID, @LaID,
@PmtAmt, @CurrentBalance - @PmtAmt);
END
END
GO

To create the stored procedure, press F5

Executing an Argumentative Stored Procedure

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

EXEC ProcedureNameParameterValue

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

EXEC ROSH.dbo.GetListOfStudentsByGender 'Male';

Here is an example of executing it:

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 stored 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:

When calling a stored 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 stored procedure. Here is an
example:

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

This would produce:

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

If you are planning to create a stored 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 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:

When executing a stored 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 stored 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 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:

When calling a stored 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 stored procedure that takes more
than one argument, you didn't have to provide the values of the arguments
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 stored 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 stored 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.

Practical Learning: Using Default Arguments

Delete the contents of the query window

To created a new version for a stored procedure we used earlier,
type the following in the window:

USE WattsALoan;
GO
DROP PROCEDURE SpecifyCurrentBalance;
GO
CREATE PROCEDURE SpecifyCurrentBalance
@PmtDate datetime,
@EmplID int,
@LaID int,
@PmtAmt money,
@Comments Text = ''
AS
BEGIN
-- Get the amount that was lent to the customer
DECLARE @AmountOfLoan money;
SET @AmountOfLoan = (SELECT las.FutureValue
FROM LoanAllocations las
WHERE (las.LoanAllocationID = @LaID));
-- If the customer had already made at least one payment,
-- get the current balance of the customer's account
DECLARE @CurrentBalance money;
SET @CurrentBalance = (SELECT MIN(pay.Balance)
FROM Payments pay
WHERE (pay.LoanAllocationID = @LaID));
-- If the customer has never made a payment (yet),
-- to specify the balance, subtract the current payment
-- from the original amount of the loan
IF @CurrentBalance IS NULL
BEGIN
INSERT INTO Payments(PaymentDate, EmployeeID,
LoanAllocationID, PaymentAmount,
Balance, Notes)
VALUES(@PmtDate, @EmplID, @LaID, @PmtAmt,
@AmountOfLoan - @PmtAmt, @Comments);
END
-- If the customer had already at least one payment,
-- subtract the current payment from the previous balance
ELSE
BEGIN
INSERT INTO Payments(PaymentDate, EmployeeID,
LoanAllocationID, PaymentAmount,
Balance, Notes)
VALUES(@PmtDate, @EmplID, @LaID,
@PmtAmt, @CurrentBalance - @PmtAmt, @Comments);
END
END
GO

Transact-SQL uses 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. 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
the stored procedure, after the name of the procedure, if you want the
stored 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:

In the body of the 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's the value that the argument will hold when the stored
procedure exits. Here is an
example:

When calling the stored 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 stored 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:

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:

When you execute this stored procedure, it would work on the
records of the table. One of the particularities of a stored 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 don't 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 stored procedure that takes an OUTPUT parameter would have a way
to isolate a result. If the stored procedure processes a SELECT statement,
you can use a WHERE condition. Here is an example of such a procedure: