stored procedure

What is stored procedure?

A precompiled set of Structured Query Language (SQL) statements that can be executed on demand by Microsoft SQL Server. Stored procedures are stored in a database.

They support features such as user-declared variables and conditional execution and can be run with a single call. They can accept parameters, and they can return parameters and status values.

They can also call other stored procedures. You can create permanent stored procedures for global administrative tasks or temporary ones for a specific task.

How It Works

You create a stored procedure by using a series of SQL statements. SQL Server parses and analyzes the stored procedure and stores it in various system tables. When you execute it for the first time, it is loaded into memory and compiled, storing the execution plan in the procedure cache. By preparsing and prenormalizing a stored procedure, you can achieve significant performance gains compared to using a simple SQL query.

You can use stored procedures with SQL Server to

• Create devices and databases
• Access or update information in database tables
• Perform other administrative or user actions

A trigger is a special type of stored procedure that you can use to enforce referential integrity in a database. Other types of stored procedures supported by SQL Server include the following:

• Extended stored procedures:
  Dynamic-link libraries (DLLs) that can be loaded and run like stored procedures

   

• Remote stored procedures:
  Run from a remote client

   

• System stored procedures:
  Included with SQL Server to simplify common administrative tasks and to obtain information from system tables

   

• User-defined stored procedures:
  Created by users for a specific database

   

• User-defined system stored procedures:
  Created by users and runnable in any database

   

Web References

• Structured Query Language (SQL)