CREATE VIEW Transactions.ShowTransactions2 AS SELECT ALL * FROM Transactions.Deposits; SELECT ALL * FROM Transactions.Withdrawals; GO
This would produce an error because a view doesn't allow SELECT statements of unrelated tables. The SQL provides another type of action called a stored procedure. Like a function, a stored procedure can be used to perform a calculation. As an alternative to a regular function, a stored procedure can produce a query. Like a view, a stored procedure can be used to create and store a query. As an alternative to a view, a stored procedure can be used to create and store many queries from tables that have nothing in common. A stored procedure solves many more problems than that (this discussion doesn't suggest in any way that a stored procedure is better or worse than either a function or a view; functions, views, and stored procedures are used to solve different types of problems).
To create a stored procedure:
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 use CREATE PROC. Both expressions produce the same result. Like everything in your database, you must name your stored procedure:
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. 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.
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 the procedure 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 EXEC or the EXECUTE keyword followed by the schema and 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:
If you have a stored procedure named GetStudentIdentification that was not created in a particular schema, to execute it, you would type:
If the stored procedure was created in a schema other than dbo, precede the name of the procedure with that schema. Here is an example:
You can also precede the name of the schema with the name of the database. Here is an example:
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:
As mentioned already, when you create a stored procedure, it becomes an object in the Object Explorer 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
As a regular Microsoft SQL Server database object, you can modify a stored procedure without recreating it. To do this:
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
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.
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.
DROP PROCEDURE [SchemaName.]ProcedureName
Of course, you should make sure you are in the right database and also that the ProcedureName exists.
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. In this case, for the stored procedure to carry its assignment, you would supply it with one or more values.
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/EXECUTE [SchemaName.]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.Registration.GetListOfStudentsByGender N'Male';
Here is an example of executing it:
Notice that we could/should have omitted the Gender column in the statement since it would be implied.
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(s). The syntax you would use is:
CREATE PROC/PROCEDURE [SchemaName.]ProcedureName @ParameterName1 DataType, @ParameterName2 DataType, @ParameterName_n DataType AS Body of the Procedure
Here is an example:
USE ROSH; GO CREATE PROCEDURE Registration.IdentifyStudentsByState @Gdr nvarchar(20), @StateOrProvince char(2) AS BEGIN SELECT FullName = LastName + ', N' + FirstName, DATEDIFF(year, DateOfBirth, GETDATE()) AS Age, Gender FROM Registration.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:
EXEC rosh.Registration.IdentifyStudentsByState N'Female', N'MD'; GO
This would produce:
Alternatively, you can provide the value for each parameter in the order of your choice. Consider the following stored procedure that takes 3 arguments:
CREATE PROCEDURE Registration.IdentifySomeStudents @Gdr nvarchar(20), @StateOrProvince nchar(2), @HomeStatus bit AS BEGIN SET NOCOUNT ON SELECT FullName = LastName + N', ' + FirstName, DATEDIFF(year, DateOfBirth, GETDATE()) AS Age, Gender FROM Registration.Students WHERE (Gender = @Gdr) AND (State = @StateOrProvince) AND (SingleParentHome = @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 Registration.IdentifySomeStudents @HomeStatus=1, @StateOrProvince=N'MD', @Gdr=N'Female'; GO
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:
CREATE DATABASE DepartmentStore3; GO USE DepartmentStore3; GO CREATE SCHEMA Inventory; GO CREATE TABLE Inventory.Categories ( CategoryID int identity(1, 1) primary key, Category nvarchar(20) not null ); GO INSERT INTO Inventory.Categories(Category) VALUES(N'Men'), (N'Women'), (N'Boys'), (N'Girls'),(N'Miscellaneous'); GO CREATE TABLE Inventory.StoreItems ( ItemNumber nvarchar(10) primary key, CategoryID int foreign key references Inventory.Categories(CategoryID), ItemName nvarchar(60) not null, Size nvarchar(20), UnitPrice money ); INSERT INTO Inventory.StoreItems VALUES(N'264850', 2, N'Long-Sleeve Jersey Dress', N'Petite', 39.95), (N'930405', 4, N'Solid Crewneck Tee', N'Medium', 12.95), (N'293004', 1, N'Cotton Comfort Open Bottom Pant', N'XLarge', 17.85), (N'924515', 1, N'Hooded Full-Zip Sweatshirt', N'S', 69.95), (N'405945', 3, N'Plaid Pinpoint Dress Shirt', N'22 35-36', 35.85), (N'294936', 2, N'Cool-Dry Soft Cup Bra', N'36D', 15.55), (N'294545', 2, N'Ladies Hooded Sweatshirt', N'Medium', 45.75), (N'820465', 2, N'Cotton Knit Blazer', N'M', 295.95), (N'294694', 2, N'Denim Blazer - Natural Brown', N'Large', 75.85), (N'924094', 3, N'Texture-Striped Pleated Dress Pants', N'44x30', 32.85), (N'359405', 3, N'Iron-Free Pleated Khaki Pants', N'32x32', 39.95), (N'192004', 3, N'Sunglasses', NULL, 15.85); GO
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 Inventory. CalculateNetPrice @discount Decimal AS SET NOCOUNT ON SELECT ItemName, UnitPrice - (UnitPrice * @discount / 100) FROM StoreItems; GO
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. 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 PROCEDURE Inventory.CalculateDiscountedPrice @discount decimal = 10.00 AS SET NOCOUNT ON 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:
On the other hand, you can ask the database engine to get the default value. To do this, pass the argument as DEFAULT. Here is an example:
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 Inventory.CalculateSalePrice @Discount decimal = 20.00, @TaxRate decimal = 7.75 AS SET NOCOUNT ON 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 have 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 CalculateSalePrice 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 CalculateSalePrice @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.
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 nvarchar(20), @LastName nvarchar(20), @Full nvarchar(42) SET @FirstName = N'Melanie'; SET @LastName = N'Johanssen'; EXECUTE Registration.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:
CREATE PROCEDURE Registration.ShowStudentsFullNames @FullName nvarchar(50) OUTPUT AS SET NOCOUNT ON SELECT @FullName = LastName + ', N' + FirstName FROM Registration.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:
DECLARE @CompleteName nvarchar(50); EXEC Registration.ShowStudentsFullNames @CompleteName OUTPUT; SELECT @CompleteName [Student Name]; GO
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:
CREATE PROCEDURE Registration.ShowStudentsFullNames @FullName nvarchar(50) OUTPUT AS SET NOCOUNT ON SELECT @FullName = LastName + ', N' + FirstName FROM Registration.Students WHERE StudentID = 8; GO
When this procedure is executed, it would produce only the record stored in the 8th position of the table.