- Start the computer and log in
- Launch Microsoft SQL Server and click Connect
- Open the WattsALoan1.sql file (Watts A Loan)
- To create the database and its objects, right-click inside the document and click Execute
- Close the Query window
- In the Object Explorer, expand the Databases node and expand WattsALoan1
- Right-click Database Diagrams -> New Database Diagram
- When the message box comes up, read it and click Yes
- Right-click Database Diagram and click New Database Diagram
- Read the message and click Yes
- In the dialog box, double-click each table and, when all tables have been added, click Close
- Close the diagram
- When asked whether you want to save it, click Yes
- Set the name to dgmWattsALoan1
- Click OK
- In the Object Explorer, right-click WattsALoan1 and click New Query
- To see a list of payments made on different loans, type the following:
SELECT ALL * FROM Loans.Payments;
GO
- To see the result, on the main menu, click Query -> Execute
- Click inside the top section of the Query window and press Ctrl + A
Creating a Stored Procedure
|
|
To create a stored procedure:
- In the Object Explorer, expand the database for which you want to create the stored 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 Stored 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
The simplest syntax to create a stored procedure in Transact-SQL is:
CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
AS
Body of the Procedure
To create a stored procedure, start with the CREATE PROCEDURE expression. You can also useCREATE PROC. Both expressions produce the same result. Like everything in your database, you must name your stored 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 stored procedure with sp_ because it could conflict with some of the stored procedures that already ship with Microsoft SQL Server
When creating a stored procedure, you can precede its name by a schema name. After the name of the procedure, type the AS keyword. The section, group of words, or group of lines after the AS keyword is the body of the stored procedure. It states what you want the procedure to do or what you want it to produce. It is important to keep in mind that there are many other issues related to creating a stored procedure but we will ignore them for now.
Probably the simplest procedure you can create would consist of selecting fields from a table. This is done with the SELECT operator. It uses the techniques we reviewed for data selection. 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:
Here is an example of creating a stored procedure:
CREATE PROCEDURE Registration.GetStudentIdentification
AS
BEGIN
SELECT FirstName, LastName, DateOfBirth, Gender
FROM Registration.Students
END
GO
Although you can use the * to select all fields of a table, instead, you should always create the list of columns, even if you plan to use all columns of a table.
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.
 Practical Learning: Creating a Stored Procedure
|
|
- To start a stored procedure, type the following:
CREATE PROCEDURE Management.ShowInformation
AS
SELECT ALL * FROM Loans.Types;
SELECT ALL * FROM Accounts.Customers;
GO
- To create the stored procedure, on the main menu, click Query -> Execute
Executing a Stored Procedure
|
|
After creating a stored procedure, you must store it as an object in your database. To do this visually, on the
SQL Editor toolbar, you can click the Execute button
. If the code of the stored procedure is right, it would be created:
Also, a new node for its name would be added to the Stored Procedures section of the database.
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 stored procedure, you use the EXECUTE keyword followed by the name of the procedure. Although there are some other issues related to executing a stored procedure, for now, we will consider that the simplest syntax to call a procedure is:
EXEC/EXECUTE [SchemaName.]ProcedureName
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. Here is an example:
EXECUTE Registration.GetStudentIdentification;
You can also precede the name of the schema with the name of the database. Here is an example:
EXECUTE rosh.Registration.GetStudentIdentification;
Practical Learning: Executing a Stored Procedure
|
|
- Click inside the top section of the Query window and press Ctrl + A to select everything
- To execute the stored procedure, type the following
EXECUTE Management.ShowInformation;
GO
- To execute the stored procedure, right-click inside the top section of the Query window and click Execute
- Click inside the top section of the Query window and press Ctrl + A
Stored Procedure and Permissions
|
|
To visually manage the permissions of a stored procedure, in the Object Inspector, right-click it and click Properties. In the left frame, click Permissions. In the right section of the dialog box, get a user name and view the available permissions in the
bottom section:
As you can see, a stored procedure does not have many permissions on its own. Still, permissions on stored procedures can get complicated because of the interactions a stored procedure has with the objects it uses.
To start, a developer who has to create one ore more stored procedures must have the CREATE PROCEDURE permission on the database that will own the procedure. Also, he or she must have theALTER permissions. You must also coordinate the permissions of the user on the objects used by the stored procedure.
Executing On Behalf of an Account
|
|
If a user is lacking some permissions when executing a stored procedure, he cannot use the result(s) of that procedure. As an alternative, when creating a certain stored procedure, you can specify the user account that will be used to execute the procedure. To give this instruction, before the AS keyword permission, type WITH EXECEUTE AS followed by the user name that will be used. The formula to follow is:
CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
WITH EXECUTE AS Option
AS
Body of the Procedure
The options are:
- If you want your own account to be used, use WITH EXECUTE AS SELF. This is the default for a stored procedure. In this case, your user name will be stored with the procedure and the databaseengine will call it when the sotred procedure is executed.
- If you want the account of the user who is executing the procedure to be used, use WITH EXECUTE AS CALLER. In this case, when the user starts executing the procedure, the database engine will check whether the user has the appropriate permissions. If so, the stored procedure would execute. Otherwise, he will be denied.
- If you want the account of the owner of the database to be used, use WITH EXECUTE AS OWNER.
- If you want to specify the account that will be used when executing the schema, use WITH EXECUTE AS, followed by the user name. Here is an example:
CREATE USER Patricia FOR LOGIN [Central\pkatts];
GO
CREATE PROCEDURE Registration.GetContactInformation
WITH EXECUTE AS N'Patricia'
AS
SET NOCOUNT ON
SELECT FirstName, LastName, ParentsNames
FROM Registration.Students;
GO
When a stored procedure executes, the database engine must keep sending messages back and forth between the server and the client. These relentless interactions create overhead on the processing and in most cases are not necessary. To avoid them, after the AS keyword, add a SET NOCOUNT ON expression before starting the body of the stored procedure. The formula to follow is:
CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
AS
SET NOCOUNT ON
Body of the Procedure
Here is an example:
CREATE PROCEDURE Registration.GetIdentification
AS
SET NOCOUNT ON
BEGIN
SELECT FirstName, LastName, DateOfBirth, Gender
FROM Registration.Students
END
GO
Although you can create and use a stored procedure without specifying a schema, it is recommended that you always use a schema. In fact, you should always create your own schema in your database and create your stored procedure in it.
Encrypting the Code of a Stored Procedure
|
|
As mentioned already, when you create a stored procedure, it becomes an object in the Object Inspector and its code is available. If you want to encrypt that code as it is stored in the database, add a WITH ENCRYPTION expression before the AS keyword. The formula to follow is:
CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
WITH ENCRYPTION
AS
Body of the Procedure
Here is an example:
CREATE PROCEDURE Registration.GetStudentIdentification4
WITH ENCRYPTION
AS
SET NOCOUNT ON
SELECT FirstName, LastName, DateOfBirth, Gender
FROM Registration.Students;
GO
Modifying a Stored Procedure
|
|
As a regular Microsoft SQL
Server database object, you can modify a stored procedure without recreating it. To do this:
- In the Object Explorer, right-click the procedure and click Modify
- In the Object Explorer, 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 Alter 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 Transact-SQL, the basic formula to modify a stored procedure is:
ALTER PROCEDURE [SchemaName.]ProcedureName
AS
Body of Procedure
Recompiling a Stored Procedure
|
|
When you create a stored procedure, it considers the state of the database at that time. This includes the columns and records in the tables, the primary keys, the foreign keys, etc. It is not unusual for a user of the database to change any of these details. After such operations have occurred, the stored procedure may not be updated and may show previous details before the changes. One way you can take care of the stored procedure is to ask the database engine to recompile the stored procedure.
To ask the database engine to check the stored procedure and recompile it the next time it is executed, when creating the procedure, before the AS keyword, add a WITH RECOMPILE expression. The formula to follow is:
CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
AS
WITH RECOMPILE
Body of the Procedure
Here is an example:
CREATE PROCEDURE Registration.GetStudentsByGender
WITH RECOMPILE
AS
SET NOCOUNT ON
SELECT FirstName, LastName, DateOfBirth, Gender
FROM Students
GO
Deleting a Stored Procedure
|
|
One of the 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 stored procedure, you can use either the Object Explorer or SQL. As mentioned with tables, even if you create a stored procedure using the Object Explorer, you can delete it using SQL and vice-versa.
To delete a stored procedure in the Object Explorer, after expanding its database, its Programmability, and its Stored Procedure nodes, 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 stored procedure in SQL, the syntax to use is:
DROP PROCEDURE [SchemaName.]ProcedureName
Of course, you should make sure you are in the right database and also that the ProcedureName exists.
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 for data selection 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:
CREATE PROCEDURE Registration.GetStudentIdentification
AS
BEGIN
SET NOCOUNT ON
SELECT FullName = FirstName + N' ' + LastName,
DateOfBirth, Gender
FROM Registration.Students
END
GO
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 selection. Here is an example of a stored procedure that calls a function:
CREATE PROCEDURE Registration.GetStudentsAges
AS
BEGIN
SET NOCOUNT ON
SELECT FullName = FirstName + N' ' + LastName,
DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
Gender
FROM Registration.Students
END
GO
Here is an example of executing the stored procedure:
EXEC rosh.Registration.GetStudentsAges;
GO
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 stored 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 orCREATE 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 PROC/PROCEDURE [SchemaName.]ProcedureName
@ParameterName DataType
AS
Body of the Procedure
When implementing the stored procedure, in the body of the procedure, you can define what you want to do with the parameter(s). One way you can use a parameter is to run a query whose statement the user would provide. For example, imagine you want to create a stored 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 PROCEDURE Registration.GetListOfStudentsByGender
@Gdr NVARCHAR(12)
AS
SELECT FirstName, LastName,
DateOfBirth, HomePhone, Gender
FROM Students
WHERE Gender = @Gdr
Practical Learning: Creating a Stored Procedure
|
|
- To pass arguments to a stored procedure, type the following in the window:
USE WattsALoan1;
GO
CREATE PROCEDURE Loans.SpecifyCurrentBalance
@PmtDate date,
@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 Loans.Allocations las
WHERE (las.AllocationID = @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 Loans.Payments pay
WHERE (pay.AllocationID = @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 Loans.Payments(PaymentDate, EmployeeID,
AllocationID, 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 Loans.Payments(PaymentDate, EmployeeID,
AllocationID, PaymentAmount, Balance)
VALUES(@PmtDate, @EmplID, @LaID,
@PmtAmt, @CurrentBalance - @PmtAmt);
END
END
GO
- To create the stored procedure, on the main, click Query -> Execute
- Click inside the top section of the Query window and press Ctrl + A
source code
