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


  • 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 )


2 thoughts on “SQL SERVER 2008 – SUB-PROGRAMS

  1. Howdy reading this blog was many interesting , topics in this way highlights who reflect this topic!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s