Tài liệu Introducing Stored Procedures doc
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (16.42 KB, 4 trang )
Introducing Stored Procedures
SQL Server allows you to store procedures in a database. Stored procedures differ from
user-defined functions in that procedures can return a much wider array of data types.
You'll typically create a stored procedure when you need to perform a task that
intensively uses the database, or you want to centralize code in the database that any user
can call rather than have each user write their own program to perform the same task.
One example of intensive database use is a banking application by which you need to
update accounts at the end of each day. One example of when you'd use centralized code
is when you want to restrict user access to database tables: you might want users to be
able to add a row to a table only through a procedure so that no mistakes are made.
In this section, you'll learn how to create a stored procedure in the Northwind database
and run it using the Query Analyzer tool.
Creating a Stored Procedure
The procedure you'll see in this section is named AddProduct(). This procedure adds a
row to the Products table, setting the column values for the new row to those passed as
parameters to the procedure.
The ProductID column for the new row is assigned a value automatically by the database
through the use of an identity that was set up when the table was originally created. This
identity value may be read using the @@IDENTITY function after the new row is added
to the table. The AddProduct() procedure you'll see here returns that identity value to the
calling statement.
You create a procedure using the CREATE PROCEDURE statement, and Listing 4.5
shows the AddProduct.sql script that creates the AddProduct() procedure.
Listing 4.5: ADDPRODUCT.SQL
/*
AddProduct.sql creates a procedure that adds a row to the
Products table using values passed as parameters to the
procedure. The procedure returns the ProductID of the new row.
*/
CREATE PROCEDURE AddProduct
@MyProductName nvarchar(40),
@MySupplierID int,
@MyCategoryID int,
@MyQuantityPerUnit nvarchar(20),
@MyUnitPrice money,
@MyUnitsInStock smallint,
@MyUnitsOnOrder smallint,
@MyReorderLevel smallint,
@MyDiscontinued bit
AS
DECLARE @ProductID int
-- insert a row into the Products table
INSERT INTO Products (
ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
Discontinued
) VALUES (
@MyProductName, @MySupplierID, @MyCategoryID, @MyQuantityPerUnit,
@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, @MyReorderLevel,
@MyDiscontinued
)
-- use the @@IDENTITY function to get the last inserted
-- identity value, which in this case is the ProductID of
-- the new row in the Products table
SET @ProductID = @@IDENTITY
-- return the ProductID
RETURN @ProductID
You can also create procedures using Enterprise Manager. You do this by clicking the
right mouse, button on the Stored Procedures node in the Databases folder and selecting
New Stored Procedure. You can then cut and paste the contents of AddProduct.sql into
the Enterprise Manager properties dialog box, as shown in Figure 4.7
. You'll notice I've
added some comments to the start of the file that indicate what the procedure does.
Figure 4.7: Using Enterprise Manager to define a procedure
You can view and modify a procedure by double-clicking the procedure name in
Enterprise Manager. You can also delete a procedure using Enterprise Manager. The
Object Browser of Query Analyzer allows you to view, modify, and delete procedures as
well.
Tip You can also delete a procedure using the DROP PROCEDURE statement, and you
can modify a procedure using the ALTER PROCEDURE statement.
In the next section
, you'll see how to run a stored procedure.
Running a Stored Procedure
You run a procedure using the EXECUTE statement. For example, the following
statements run the AddProduct() procedure:
DECLARE @MyProductID int
EXECUTE @MyProductID = AddProduct 'Widget', 1, 1, '1 Per box', 5.99, 10, 5, 5, 1
PRINT @MyProductID
With the initial set of rows in the Products table, the next identity value generated by
SQL Server for the ProductID is 78, which is the value displayed by the previous
example if you run it.
You can of course also pass variables as parameters to a procedure. The following
example displays 79-the next ProductID:
DECLARE @MyProductID int
DECLARE @MyProductName nvarchar(40)
DECLARE @MySupplierID int
DECLARE @MyCategoryID int
DECLARE @MyQuantityPerUnit nvarchar(20)
DECLARE @MyUnitPrice money
DECLARE @MyUnitsInStock smallint
DECLARE @MyUnitsOnOrder smallint
DECLARE @MyReorderLevel smallint
DECLARE @MyDiscontinued bit
SET @MyProductName = 'Wheel'
SET @MySupplierID = 2
SET @MyCategoryID = 1
SET @MyQuantityPerUnit = '4 per box'
SET @MyUnitPrice = 99.99
SET @MyUnitsInStock = 10
SET @MyUnitsOnOrder = 5
SET @MyReorderLevel = 5
SET @MyDiscontinued = 0
EXECUTE @MyProductID = AddProduct @MyProductName,
@MySupplierID, @MyCategoryID, @MyQuantityPerUnit,
@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder,
@MyReorderLevel, @MyDiscontinued
PRINT @MyProductID
