Implementing Stored Procedures
Stored Procedures are the most-used programmatic structures within a database. When you think of a procedure just think of a batch of code that is given a name, when that name is called the batch of code will be executed.
S.P.’s can return scalar values or actual result sets and are the primary interface that applications should use to access any data within a database. S.P.’s you the ability to control access to a database as well as gives you an easy way to maintain your code if changes need to be made to an application in the future.
Each procedure must have a unique name within the database. You can specifiy input paramaters and output parameters for the procedure. Inputs will be used as local variables within the procedure, and Outputs will be passed back to the routine that called the procedure.
Applying security within SQL server using stored procedures provides a very powerful secuirty mechanism. Any user granted execute permissions on a stored procedure is automatically delegated permissions to the object and commands referenced inside the stored procedure based on the permissoin set of the user who created the stored procedure. If all data access (insertions, deletions, updates, or selects) were performed through stored procedures, users could not directly access any table in the database. Only by executing stored procedures would users be able to manage the database.
A Microsoft best practice is to use the minimum amount of code that also minimizes the amount of work performed by the system.
Now lets practice. The practice uses the AdventureWorks sample database which can be downloaded from the web.
We want to create a stored procedure to update the hire date of employees.
CREATE PROCEDURE dbo.usp_UpdateEmployeeHireDate
AS
DECLARE @now DATETIME
–Above creates a store procedure and declares a DATETIME value to be used within the procedure.
SET @now = GETDATE()
–Above sets the current date to the value.
UPDATE HumanResources.Employee
SET HireDate = @now
–Above updates the Employee tables hiredate to the value.
