Sql server® 2012 t-sql fundamentals

Stored procedures

Stored procedures are server-side routines that encapsulate T-SQL code. Stored procedures can have 

input and output parameters, they can return result sets of queries, and they are allowed to invoke 

code that has side effects. Not only can you modify data through stored procedures, you can also 

apply schema changes through them.

Compared to using ad-hoc code, the use of stored procedures gives you many benefits:

■

■

Stored procedures encapsulate logic.  If you need to change the implementation of a 

stored procedure, you can apply the change in one place in the database and the procedure 

will be altered for all users of the procedure.

■

■

Stored procedures give you better control of security.  You can grant a user permissions 

to execute the procedure without granting the user direct permissions to perform the under-

lying activities. For example, suppose that you want to allow certain users to delete a customer 

from the database, but you don’t want to grant them direct permissions to delete rows from 

the Customers table. You want to ensure that requests to delete a customer are validated—for 

example, by checking whether the customer has open orders or open debts—and you may 

also want to audit the requests. By not granting direct permissions to delete rows from the 

Customers table but instead granting permissions to execute a procedure that handles the 

task, you ensure that all the required validations and auditing always take place. In addition, 

stored procedures can help prevent SQL injection, especially when they replace ad-hoc SQL 

from the client with parameters.

■

■

You can incorporate all error handling code within a procedure, silently taking correc-