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 | 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 shows the script that creates the AddProduct procedure. 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 .
