SQL SERVER 2008 – SUB-PROGRAMS

A Sub-Program is a new block of code which can be reused. We have 2 types of Sub-Programs in SQL Server:

1. Procedures
2. Functions

Procedures:

• A stored procedure is a saved collection of Transact-SQL statements or a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters.
• Procedures can be created for permanent use or for temporary use within a session, local temporary procedure, or for temporary use within all sessions, global temporary procedure.
• Stored procedures can also be created to run automatically when an instance of SQL Server starts.

Functions:

• A Function is also Stored Block of code similar to a Procedure.
• A Function is a Block of Code which will return only a single value.
• A Function is not a stand alone executable like a Procedure it can be executed in some other context also.
• A Function can be used in a Select Statement.
• Modifications to database tables, operations on cursors that are not local to the function are examples of actions that cannot be performed in a function.
• Try and Catch Statements cannot be used in the Functions.
• A user-defined function takes zero or more input parameters and returns either a scalar value or a table; a function can have a maximum of 1024 input parameters.
• User-defined functions do not support output parameters.
• When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )