Stored Procedures are special types of functions that can execute under the SQL Server Environment. SPs are said to be building blocks of any web application / distributed / SaaS/client-server applications. There are several basic advantages in using stored procedures.
- Increases the Modularity.
- Can serve as a Middle Tier / Business Logic in Multi-Tier Applications.
- Uses the Network Traffic Efficiently.
- Since they in Pre Compiled Format the execution is faster (Not Applicable for Procedures with 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.
How Stored Procedure Works internally?
Stored Procedures does have a 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 look for the existence of the objects referred to in your procedure. So you can have total freedom to refer to any Dynamic Objects that may be created at runtime.
The main advantage of using stored procedure is the effective utilization of the server’s resources when compared to issuing serious SQL Commands. Though there are is considerable advantage in using cached execution plans, both SQL Server 2000 and 2005 are 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 that won’t operate on a 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 the sp_helptext command. This type of stored procedure does have some disadvantages as it cant be published for Replication.
Temporary Stored Procedures
Temporary Stored Procedures are a new set of features added to SQL Server 2000 and they can be created by 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 series 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 Begin --Write SQL Insert Query End WHEN ‘U’ Then Begin --Write SQL Update Query End WHEN ’S’ Then Begin -- 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. End
Note: This kind of procedure will be recompiled for every combination and care should be taken while writing this kind of procedure as it cause performance havocs.
Next: » Calling stored procedures from ASP.NET and VB.NET