Programming in MS SQL Server 2012

Types of Stored Procedures

User-defined Stored Procedures
A user-defined procedure can be created in a user-defined database or in all system databases except the resource database. The stored procedure can be developed in either Transact-SQL or as a reference to a Microsoft .NET Framework common runtime language (CLR) method.
Temporary Stored Procedures
Temporary stored procedures are a form of user-defined procedures. Temporary stored procedures can categories into Local Temporary Stored Procedure or Global Temporary Stored Procedure.
The Local Temporary Stored Procedures have names prefixed with the # symbol that stored procedures are stored in the tempdb database. They are visible only to the current user connection. Local Temporary stored procedures are automatically dropped when the client connection to the server terminates.
Global Temporary Stored Procedures have names prefixed with the ## symbols that stored procedures are stored in the tempdb database. They are visible to any user after they are created. Global Temporary stored procedures are automatically dropped when at the end of the last session using the stored procedure.
System Stored Procedures
System Stored Procedures are included with SQL Server. They are physically stored in the internal, hidden resource database and logically appear in the sys schema of every system and user-defined database. In addition, the msdb database also contains system stored procedures in the dbo.schema that are used for scheduling alerts and jobs.
System stored procedures are start with the prefix sp_, and try to avoid this prefix when naming user_define stored procedures. SQL Server supports the system stored procedures that provide an interface form SQL Server to external programs for various database maintenance activities.
Extended User-Defined Stored Procedures
Extended Stored Procedures enable creating external routines in a programming language such as C programming language. These procedures are DLLs that an instance of SQL Server can dynamically load and run.
Note: Extended stored procedures will be removed in a future version of SQL Server. Create CLR stored procedures instead that provides a more robust and secure alternative to writing extended stored procedures.

* * * * *

Your Comment To AUTHOR