Introducing Stored Procedures

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
Nội dung trích xuất từ tài liệu:
Introducing Stored Procedures Introducing Stored ProceduresSQL Server allows you to store procedures in a database. Stored procedures differ fromuser-defined functions in that procedures can return a much wider array of data types.Youll typically create a stored procedure when you need to perform a task thatintensively uses the database, or you want to centralize code in the database that any usercan 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 toupdate accounts at the end of each day. One example of when youd use centralized codeis when you want to restrict user access to database tables: you might want users to beable to add a row to a table only through a procedure so that no mistakes are made.In this section, youll learn how to create a stored procedure in the Northwind databaseand run it using the Query Analyzer tool.Creating a Stored ProcedureThe procedure youll see in this section is named AddProduct(). This procedure adds arow to the Products table, setting the column values for the new row to those passed asparameters to the procedure.The ProductID column for the new row is assigned a value automatically by the databasethrough the use of an identity that was set up when the table was originally created. Thisidentity value may be read using the @@IDENTITY function after the new row is addedto the table. The AddProduct() procedure youll see here returns that identity value to thecalling statement.You create a procedure using the CREATE PROCEDURE statement, and Listing 4.5shows 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 bitASDECLARE @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 @ProductIDYou can also create procedures using Enterprise Manager. You do this by clicking theright mouse, button on the Stored Procedures node in the Databases folder and selectingNew Stored Procedure. You can then cut and paste the contents of AddProduct.sql intothe Enterprise Manager properties dialog box, as shown in Figure 4.7. Youll notice Iveadded some comments to the start of the file that indicate what the procedure does.Figure 4.7: Using Enterprise Manager to define a procedureYou can view and modify a procedure by double-clicking the procedure name inEnterprise Manager. You can also delete a procedure using Enterprise Manager. TheObject Browser of Query Analyzer allows you to view, modify, and delete procedures aswell.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, youll see how to run a stored procedure.Running a Stored ProcedureYou run a procedure using the EXECUTE statement. For example, the followingstatements run the AddProduct() procedure:DECLARE @MyProductID intEXECUTE @MyProductID = AddProduct Widget, 1, 1, 1 Per box, 5.99, 10, 5, 5, 1PRINT @MyProductIDWith the initial set of rows in the Products table, the next identity value generated bySQL Server for the ProductID is 78, which is the value displayed by the previousexample if you run it.You can of course also pass variables as parameters to a procedure. The followingexample displays 79-the next ProductID:DECLARE @MyProductID intDECLARE @MyProductName nvarchar(40)DECLARE @MySupplierID intDECLARE @MyCategoryID intDECLARE @MyQuantityPerUnit nvarchar(20)DECLARE @MyUnitPrice moneyDECLARE @MyUnitsInStock smallintDECLARE @MyUnitsOnOrder smallintDECLARE @MyReorderLevel smallintDECLARE @MyDiscontinued bitSET @MyProductName = WheelSET @MySupplierID = 2SET @MyCategoryID = 1SET @MyQuantityPerUnit = 4 per boxSET @MyUnitPrice = 99.99SET @MyUnitsInStock = 10SET @MyUnitsOnOrder = 5SET @MyReorderLevel = 5SET @MyDiscontinued = 0EXECUTE @MyProductID = AddProduct @MyProductName, @MySupplierID, @MyCategoryID, @MyQuantityPerUnit, @MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, @MyReorderLevel, @MyDiscontinuedPRINT @MyProductID