A Sub-Program is a new block of code which can be reused. We have 2 types of Sub-Programs in SQL Server:
- 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.
- 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 )