Stored Procedures

The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:

• They are registered at the server.
• They can have security attributes (such as permissions) and ownership chaining, and certificates can be attached to them.
  Users can be granted permission to execute a stored procedure without having to have direct permissions on the objects referenced in the procedure.
• They can enhance the security of your application.
  Parameterized stored procedures can help protect your application from SQL Injection attacks.
• They allow modular programming. You can create the procedure once, and call it any number of times in your program. This can improve the maintainability of your application and allow applications to access the database in a uniform manner.
• They can reduce network traffic. An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

There are many types of stored procedures available in Microsoft SQL Server 2005.

1. User-defined Stored Procedures

A Transact-SQL stored procedure is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.

A CLR stored procedure is a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters. They are implemented as public, static methods on a class in a .NET Framework assembly.

2. Extended Stored Procedures

                   Extended stored procedures let you create your own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.

3. System Stored Procedures

Many administrative activities in SQL Server 2005 are performed through a special kind of procedure known as a system stored procedure. System stored procedures are physically stored in the Resource database and have the sp_ prefix. System stored procedures logically appear in the sys schema of every system- and user-defined database. In SQL Server 2005, GRANT, DENY, and REVOKE permissions can be applied to system stored procedures.

Note

The Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.