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.

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

How Stored Procedure Works internally?

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.

Stored Procedure a Primer
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.

Advanced Options in Stored Procedure Creation

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

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.

Optional Parameters in Stored Procedure

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.

Create Procedure ProcessAuthors (@OpType char(1), @Auth_ID numeric(3,0) =0, @Author_name varchar(100)=’’, @Author_Location varchar(100)=’’) as

CASE @OpType

WHEN ’I’ Then
--Write SQL Insert Query

End WHEN ‘U’ Then
--Write SQL Update Query

End WHEN ’S’ Then
-- Since we have Optional Parameters, while selecting records we just need to give the OpType Parameter and you can extend the selection by using another CASE statement for making selection based on all the criteria.


Note: This kind of procedures will be recompiled for every combination and care should be taken while writing these kind of procedures as it cause performance havocs.
Next: » Calling stored procedures from ASP.NET and VB.NET


Related Resources

A to Z of stored procedures for ASP and VB Programmers

A to Z of stored procedures for ASP and VB Programmers

Web Databases

The article is meant for ASP and VB Programmers, who always try to write hard core SQL statements embedded in their coding to communicate with

Using Stored procedure with mySQL and PHP

Using Stored procedure with mySQL and PHP

Web Databases

Writing external scripts to perform complex data handling is a tedious affair. The best way to automate tasks straightaway into the server is by using

Stored Procedures Tips

Stored Procedures Tips

Web Databases

Here is some practice tips which you can remember and apply when writing SPs Use SET NOCOUNT ON, this will reduce unnecessary return values as