Stored Procedures a Primer

Stored Procedures are typical special type of functions that can execute under the SQL Server Environment. SP's are said to be building blocks of any we application / distributed / client server applications. There are several basic advantages in using stored procedures.

• Increases the Modularity.
• Can serve as a Middle Tier / Business Logic in a Multi Tier Applications.
• Uses the Network Traffic Efficiently.
• Since they in Pre Compiled Format the execution is faster (Not Applicable for Procedures wit RECOMPILE).

SQL Server does have Different Category of Procedures Available.

Category Description Active Directory Procedures Used to register instances of SQL Server and SQL Server databases in Microsoft Windows® 2000 Active Directory™. Catalog Procedures Implements ODBC data dictionary functions and isolates ODBC applications from changes to underlying system tables. Cursor Procedures Implements cursor variable functionality. Database Maintenance Plan Procedures Used to set up core maintenance tasks necessary to ensure database performance. Distributed Queries Procedures Used to implement and manage Distributed Queries. Full-Text Search Procedures Used to implement and query full-text indexes. Log Shipping Procedures Used to configure and manage log shipping. OLE Automation Procedures Allows standard OLE automation objects to be used within a standard Transact-SQL batch. Replication Procedures Used to manage replication. Security Procedures Used to manage security. SQL Mail Procedures Used to perform e-mail operations from within SQL Server. SQL Profiler Procedures Used by SQL Profiler to monitor performance and activity. SQL Server Agent Procedures Used by SQL Server Agent to manage scheduled and event-driven activities. System Procedures Used for general maintenance of SQL Server. Web Assistant Procedures Used by the Web Assistant. XML Procedures Used for Extensible Markup Language (XML) text management. General Extended Procedures Provides an interface from SQL Server to external programs for various maintenance activities.

Apart from the above list of procedures, we can write our own Custom Stored Procedures.

Stored Procedures does have simple life cycle and it begins with the Create Proc statement, once the set of statements has been successfully passed the syntax checking the name of the procedure is stored in the sysobjects table and the entire procedure commands / statements will be stored in syscomments table.

As you can notice from the above diagram while creating the stored procedures, the compiler would just perform Syntax Checking and it doesn’t looks for the existence of the objects referred in your procedure. So you can have total freedom to refer any Dynamic Objects that may be created at runtime.

The main advantage of using stored procedure is the effective utilization of server’s resources when compared to issuing serious of SQL Commands. Though there are is considerable advantage in using cached execution plans, both SQL Server 2000 and 2005 is designed to catch all the TSQL Batch Statements regardless of whether they are stored procedures or not.

Apart from this, there are other advantages that you can consider while using stored procedures.

As we all know the basics of creating stored procedures, here we will be looking few advanced options such as

• WITH RECOMPILE:- This option allows us to define a dynamic stored procedure which won’t operate on pre compiled plan and when used this procedure is recompiled at the time of execution.
• WITH ENCRYPTION:- This option allows us to encrypt a stored procedure and we can’t view the stored procedure with sp_helptext command. This type of stored procedures does have some disadvantages as it cant be published for Replication.

Temporary Stored Procedures are new set of features added to SQL Server 2000 and they can be create as like creating temporary tables using “#” or “##” before the procedure name. All these temporary procedures are stored in tempdb and care must be taken not to put big procedures as temporary procedures. Small Procedures designed using WITH RECOMPILE Option can be replaced by these temporary stored procedures. But if you really need to execute serious of dynamic SQL Statements, it is advised to use sp_executesql.

A Stored Procedure can be created with a default value for a parameter which we can also refer as Stored Procedures with optional parameters. Suppose if you need to design a common procedure for insertion, updating and selection of records, and this procedure can be used to filter records using all the columns, here is a way to do it.