SQL Server Stored procedure is similar to procedures and sub routines in other programming languages. Stored Procedures have advantage over other SQL object because they have ability to modify data. They can accept parameters and return record set or a value using output parameters. They can improve security and we could let users to interact with multiple tables they no necessary have individual permissions.
There are different types of stored procedure
We could categorize them to user defined and system stored procedures.
If we can categorize them to the environment they are written we have
Transact – SQL: stored procedures are the most common ones and they are written using t-SQL.
CLR stored procedures: these stored procedures are written using .NET Framework public static methods.
Extended Stored Procedures: these procedures are written using external programming languages DLL files. These stored procedures are deprecated and might not be available in future versions of SQL Server.
Natively Compiled Stored Procedures: These are special types of Transact-SQL procedures compiled to support memory-optimized tables. These procedures will be available in SQL Server 2014.
In the coming blogs we will see an example for each one of them.
Read More:
http://technet.microsoft.com/en-us/library/ms191436(v=sql.105).aspx
