SQL Server - Tell me the difference between Stored Procedure and Extended Stored Procedure

can any one tell me the difference between stored procedure and extended stored procedures

Stored Procedures

A Transact-SQL stored procedure is a set of T-SQL code that is stored in a SQL Server database and compiled when used. You create this set of code using the CREATE PROCEDURE command. You can use most Transact-SQL commands in a stored procedure; however, some commands (such as CREATE PROCEDURE, CREATE VIEW, SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL, and so forth) must be the first (or only) statement in a command batch, and therefore aren't allowed in stored procedures. Most Transact-SQL commands behave the same in a stored procedure as they do in a command batch, but some have special capabilities or exhibit different behavior when executed within the context of a stored procedure.

Extended Stored Procedures

Extended procedures are routines residing in DLLs that function similarly to regular stored procedures. They receive parameters and return results via SQL Server's Open Data Services API and are usually written in C or C++. They must reside in the master database and run within the SQL Server process space.

Although the two are similar, calls to extended procedures work a bit differently than calls to system procedures. Extended procedures aren't automatically located in the master database and they don't assume the context of the current database when executed. To execute an extended procedure from a database other than the master, you have to fully qualify the reference (e.g., EXEC master.dbo.xp_cmdshell 'dir').

References:

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=1&rl=1

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=7&rl=1

SQL Server Helper
http://www.sql-server-helper.com

What is the difference between a stored procedure and an extended stored procedure?
Stored procedures are batches of Transact-SQL code that are parsed, compiled and stored in SQL Server system tables. The advantages of stored procedures is that SQL Server will create an execution plan for each stored procedure which will optimize the repeated use of the stored procedure. This execution plan may continually be held in the stored procedure cache until the cache is emptied or it may periodically drop from the cache depending on its usage verses the usage of the other stored procedure on your server.

Extended stored procedures are dynamic-link libraries (DLLs) that SQL Server can dynamically load and execute and allow you to create your own external routines in another programming language, such as C, for use in SQL Server. These extended stored procedures appear to users as normal stored procedures and are executed in the same way and can make the use of parameters as well as return results sets and status.

reference

http://www.transactsql.com/html/storprocfaq.html