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.

1. Make sure you have the Yugo National Bank database created in the Lesson13. If you didn't create it, do it now.
   In the Object Explorer, right-click YugoNationalBank and click New Query
2. To start a stored procedure, type the following:
    

   USE YugoNationalBank; GO -- ============================================= -- Author: FunctionX -- Create date: Friday, May 25, 2007 -- Description: This stored procedure assigns a -- default password to each employee. -- ============================================= CREATE PROCEDURE AssignDefaultPassword AS BEGIN UPDATE dbo.Employees SET Password = 'Password1' FROM dbo.Employees; END GO

3. To create the stored procedure, press F5

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

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

   EXECUTE AssignDefaultPassword; GO

2. To execute the stored procedure, press F5

1. Delete the contents of the query window
2. To start a new stored procedure, type the following:
    

   USE YugoNationalBank; GO -- ============================================= -- Author: FunctionX -- Create date: Friday, May 25, 2007 -- Description: This stored procedure creates a -- username for each employee. -- It also assigns an email to the employee. -- ============================================= CREATE PROCEDURE CreateUsername AS BEGIN UPDATE dbo.Employees SET Username = LOWER(LEFT(FirstName, 1) + LEFT(LastName, 5)) FROM dbo.Employees; UPDATE dbo.Employees SET EmailAddress = LOWER(LEFT(FirstName, 1) + LEFT(LastName, 5)) + '@yugonationalbank.com' FROM dbo.Employees; END GO

3. To create the stored procedure, press F5
4. Delete the contents of the query window and replace it with the following:
    

   EXECUTE CreateUsername; GO

5. To execute the stored procedure, press F5

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.

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:

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

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 ProcedureName ParameterValue

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:

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

Here is an example:

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

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:

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

When calling 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', @Gdr='Female';

Here is an example of executing the procedure:

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

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, UnitPrice - (UnitPrice * @discount / 100)
FROM StoreItems

This can be executed as follows:

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:

CREATE PROC CalculateDiscountedPrice
@discount decimal = 10.00
AS
    SELECT ItemName, UnitPrice - (UnitPrice * @discount / 100)
    FROM StoreItems;
GO

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:

CREATE PROC 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;
GO

Here is an example of executing the procedure:

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.

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:

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

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:

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

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:

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;
GO

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

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.