Description
Basics
A stored procedure is a database object that can store several SQL commands in a single executable script. It can receive parameters, set variables, check for errors and incorporate branching (IF) and looping structures. Stored procedures also provide a powerful tool for protecting database integrity and security. It is possible to grant a user permissions to execute a stored procedure while not granting that user permission to access the underlying tables. This allows the database administrator to control the ways in which any user can access a database.
Stored procedures are database specific. SQL Server's Transact SQL(T-SQL) supports them as does Oracle's Procedural Language SQL (PL-SQL), but MySQL and Access do not support them. MySQL 5.0 added stored procedure support to MySql, and Access' parameter queries have some of the qualities of a stored procedure.
In SQL Server the basic pattern for a stored procedure is:
CREATE PROC [name of procedure] [@parameter1] [datatype], [@parameter2] [datatype] AS [SQL statements and any variables]
You can have as many parameters as you need. They are passed to the procedure when it is called. All parameters and variables in a stored procedure start with the @ sign. Built-in fuctions such as @@Identity which returns the last identity (counter) use double @ signs.
Here is a simple stored procedure using Northwind:
Create proc usp_EmployeeSales @Lastname nvarchar(20) AS Select Lastname, count(orderid) From Employees e Inner join Orders o On e.Employeeid=o.employeeid Where lastname=@Lastname group by Lastname
@Lastname is a parameter. It must be passed to the stored procedure when you call it.
usp_EmployeeSales 'Davolio'
You can procede the name of the stored procedure with the command exec if you wish. the procedure runs the same with or without it, but the exec key word may make it clearer in the code that you are executing a procedure. The results look like this:
Davolio 123
Sample Database
For the rest of the Stored Procedure examples we will use a database called SimpleInventory. The database consists of 4 tables: a Customer table, an Iinventory table, a Sales and a SaleDetail table.
Here is the code to create the database. You can copy it from this document and paste it into the query analyzer, or go to the ITC 222 syllabus on my web site and download the script under Sample Code.
/***********************************
this script creates the database
simple inventory. It consists of four
tables a few records. Its purpose is
to provide a simple test database
for creating stored procedures and
triggers
Steve Conger Spring 2005
***********************************/
Create database SimpleInventory
go
use SimpleInventory
go
--begin tables
Create table Customer
(
Customerid int identity(100,1) primary key,
lastname varchar(20) not null,
firstname varchar(20) not null
)
go
create table Inventory
(
InventoryID int identity(1000,1) primary key,
InventoryItem varchar(40) not null,
InventoryPrice money not null,
InventoryInStock int not null
)
go
Create table Sale
(
SaleID int identity(1,1)primary key,
SaleDate datetime not null,
CustomerID int not null,
Constraint FKCustomer Foreign Key(customerid)
References Customer(customerID)
)
go
Create table SaleDetail
(
SaleDetailID int identity(1,1) primary key,
SaleID int not null,
InventoryID int not null,
Quantity int not null,
Constraint FKSale Foreign key(saleid)
References Sale(saleid),
Constraint FKInventory Foreign Key(InventoryID)
References Inventory(InventoryID)
)
--begin insert into tables
go
--inserts into customer
Insert into customer(lastname, firstname)
values ('Sanders', 'Sara')
Insert into customer(lastname, firstname)
values ('williams', 'Mark')
Insert into customer(lastname, firstname)
values ('Barnes', 'Nobel')
--Insert into inventory
Insert into Inventory(InventoryItem, InventoryPrice, InventoryInStock)
Values ('Plasma TV', 1870.00,10)
Insert into Inventory(InventoryItem, InventoryPrice, InventoryInStock)
Values ('Laptop', 1270.00,10)
Insert into Inventory(InventoryItem, InventoryPrice, InventoryInStock)
Values ('XBox', 288.50,10)
Insert into Inventory(InventoryItem, InventoryPrice, InventoryInStock)
Values ('Ipod', 279.00,10)
Insert into Inventory(InventoryItem, InventoryPrice, InventoryInStock)
Values ('DVD Recorder', 377.00,10)
Adding Variables
Lets create a more complex and useful stored procedure. We will do it over several iterations, each time adding features that make it more useful. The basic idea is to process a sale. We will start by assuming the customer already exists in the database and that we know his or her ID. To complete the sale we must
- Insert it and the current date into the sale table
- Get the new saleid
- Insert the saleid , the InventoryID, and the quantity sold into the saledetail table
- Subtract the quantity sold from the InventoryInStock
For now we will assume there is only one item for each sale in the saledetail table. First we will create the new procedure and assign the parameters
Create proc usp_NewSale @CustomerID int, @InventoryID int, @Quantity int
Now we will start the procedure itself using the AS keyword. the first task is to declare a variable to get the current date and time. Parameters are always declared before the AS, Variables are declared after the AS and must use the DECLARE keyword. To assign a value to a variable you must use a SELECT statement.
As Declare @today datetime Select @today= GetDate()
Now We will do the first insert using the parameter @CustomerID and the variable @today.
Insert into Sale(SaleDate, CustomerID) Values(@today, @customerid)
Now we need to insert into the SaleDetail table. To do that we need to know the SaleId of the sale we just inserted. We can declare a new variable and assign to it the value retrieved by the built in function @@Identity. @@Identity returns the last generated Identity number in the database. With this we can insert into sale details.
Declare @saleid int Select @saleid=@@identity Insert into SaleDetail(saleid,InventoryID,Quantity) Values(@saleid, @inventoryID, @quantity)
Finally we need to update the Inventory.
Update Inventory Set InventoryInStock=InventoryInStock-@Quantity where InventoryID=@inventoryID
Run the SQL to create the stored procedure. If you have errors, correct them. If the stored procedure SQL runs but there are still problems to correct, change the CREATE PROC to ALTER PROC and make the changes and then run the SQL again.
To execute the Procedure state its name and the list the parameter value in the order they are declared.
usp_NewSale 101,1001,3
Alternatively, you can assign the values directly to the parameter name and then the sequence of values doesn’t matter. (Warning, each time you run this you will subtract an additional 3 items from inventory item 1001)
EXEC usp_NewSale @CustomerID=101, @InventoryID=1001, @Quantity=3
Now do some selects to see the results.
Select * from Inventory Select * from Sale select * from SaleDetail
If and Exists
Now we will alter the store procedure to test whether the customer exists in the database. and we will use the names of the customers rather than the IDs. This introduces the new keyword IF, EXISTS, BEGIN, END, ELSE. The BEGIN and END keywords mark the beginning and ending of blocks of code. Here is the complete stored procedure with SQL comments to help explain what is going on.
The code is fairly complex. You can either type it into the Query analyzer or cut and paste it. Try to identify what each section is doing.
Alter proc usp_NewSale --add new parameters @Lastname varchar(20), @firstname varchar(20), @InventoryID int, @Quantity int As Declare @today datetime Select @today= GetDate() --declare customerid as variable Declare @CustomerID int --set up a condition if the name --exists do one thing --if not do another IF Exists (SELECT Lastname, firstname FROM Customer WHERE Lastname=@lastname AND firstname=@firstname) --begin the block for if the name does exist Begin --find the existing id Select @CustomerID=Customerid FROM Customer Where lastname=@lastname And firstname=@firstname end --end the block for does exist Else --start the block for does not exist Begin --insert the new customer Insert into Customer (lastname, firstname) Values (@Lastname, @firstname) --get the identity Select @customerID=@@identity end --end the block for does not exist --do the remaining inserts and updates Insert into Sale(SaleDate, CustomerID) Values(@today, @Customerid) Declare @saleid int Select @saleid=@@identity Insert into SaleDetail(saleid,InventoryID,Quantity) Values(@saleid, @inventoryID, @quantity) Update Inventory Set InventoryInStock=InventoryInStock-@Quantity where InventoryID=@inventoryID
When you get the stored procedure error free, run it with the following parameters
exec usp_Newsale 'zukof', 'Albert', 1002, 1
Now run these selects to view the results.
Select * from customer Select * from Inventory Select * from Sale select * from SaleDetail
Now run it again with these parameters (an existing customer) and do the selects to see the results
exec usp_Newsale 'Barnes', 'Nobel', 1002, 1
Error Trapping and Transactions
So far. we have added a simple branching to handle two different situations: one where the customer exists, and one where the customer does not exit. It would be very useful to add a transaction to the stored procedure so that if there are any errors the whole thing could be rolled back. Otherwise we could end up with a sale without sale details, or the Inventory might not get updated. So we will adopt the stored procedure to include transactions and error trapping. SQL Server contains a special function called @@Error which returns an error number. The @@Error function only returns the error number of the last executed statement, so we must check each statement for errors and then assign them results to some sort of counter.
Let's look at a simpler version of the stored procedure above. We will just insert into the sale and the saledetail tables (not even worrying about updating the Inventory table). Here is the simplified procedure with comments.
Create proc usp_Errortest --the parameters @Customerid int, @InventoryID int AS --Declare some variables Declare @SaleDate DateTime Select @SaleDate=GetDate() Declare @SaleID int --begin the transaction Begin tran --declare err and errcount variables Declare @err int Declare @errcount int --set its initial value to 0 Set @errcount = 0 --insert into Sale Insert into sale (Customerid, Saledate) Values (@CustomerID, @SaleDate) --check for an error Select @err=@@error --if there is an error add one to the counter if (@err <> 0) Set @errcount=@errcount+1 --insert into SaleDetail Insert into SaleDetail(Saleid, InventoryID, Quantity) Values(@saleid, @inventoryID, 1) --check for errors Select @err=@@error If (@err <> 0) set @errcount=@errcount+1 --Check the error count if it is --not 0 then rollback the transaction If (@errcount <> 0) Begin rollback tran Print 'the record was not inserted' end Else --otherwise commit it Begin Commit tran End
Try this with the values 100 for the customer and 1007 for the InventoryID. (The inventoryID doesn’t exist). Try it with a bad customer number. Try it with legitimate numbers.
Try Catch
SQL server 2005 adds the ability to use try catch blocks. This can simplify error handling greatly. Below is the same query as above but with the try catch error trapping
Create proc usp_Errortest --the parameters @Customerid int, @InventoryID int AS --Declare some variables Declare @SaleDate DateTime Select @SaleDate=GetDate() Declare @SaleID int --begin the transaction Begin tran --begin the try Begin Try --insert into Sale Insert into sale (Customerid, Saledate) Values (@CustomerID, @SaleDate) --insert into SaleDetail Insert into SaleDetail(Saleid, InventoryID, Quantity) Values(@saleid, @inventoryID, 1) Commit Tran End Try --catch any errors Begin Catch rollback tran Print 'the record was not inserted' End catch