Stored Procedures in SQL Server

As the Name implies stored procedures are data base objects which store the business logic or procedure in a complied script. These are special data base objects. They are pre-compiled T-SQL statements/queries.

Entire business logic of a process can be encapsulated in a stored procedure and this can be used when ever needed. This will reduce the overhead of writing the same queries again and again. Executing stored procedures is comparatively faster than executing the queries independently. Also, stored procedures use less bandwidth. That is the reasons stored procedures are most preferred for the cross applications.

Syntax for the stored procedures is:

CREATE PROCEDURE <Procedure Name>
<Input Parameters comma separated> , <Output parameters comma separated>
AS
BEGIN
< T-SQL Statements >
END

Stored procedures can be classified broadly into 3 categories:

  1. System stored procedures
  2. User Defined stored procedures
  3. Extended stored procedures

User defined stored procedures are again classified into the following categories:

  1. Stored procedures without input and output parameters (Simple stored procedures)
  2. Stored procedures with input parameters and without output parameters
  3. Stored procedures with input and output parameters.

We will discuss in detail about all these stored procedure types in the coming sessions.

This entry was posted in . Bookmark the permalink.

Leave a reply