Type of SP

There are many type of stored procedure, but broadly we can classify it into three categories.

1.    User define stored procedure

2.    External stored procedure

3.    System stored procedure

User define stored procedure:

This type is mostly a routine or bundle of individual work grouping together to perform some set of works.

It again divided into 2 types

a.    Transact-SQL

b.    CLR

In transact-SQL contain Transact-SQL statements that works together to performs a specific tasks. Such as, a stored procedure to perform some DML or DDL activity.

A CLR stored procedure is a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters. They are implemented as public, static methods on a class in a .NET Framework assembly

External Stored procedure:

It is used to create external routines by any kind of programming language like C.

External stored procedures are a DLL that dynamically load by SQL server and run.

External stored procedures run dynamically in the same memory space where the program that runs the external stored procedures API runs.

It generally takes xp_ prefix.

System Stored procedures:

Many administrative tasks is performed by it.

For example sys.sp_helptrigger is a system procedure. The system stored procedure is always prefixed by sp_. They logically appear at sys schema of every systems and user define database.

For better understanding please refer to MSDN blog

http://msdn.microsoft.com/en-us/library/ms187644(v=sql.90).aspx

Posted by : MR.JOYDEEP DAS