Refer MSDN
Stored Procedure :
A stored procedurehttp://msdn.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx#sql:stored_procedure is a group of Transact-SQL statements compiled into a single execution plan.
Microsoft® SQL Server™ 2000 stored procedures return data in four ways:
- Output parameters, which can return either data
(such as an integer or character value) or a cursor variable (cursors
are result sets that can be retrieved one row at a time).
- Return codes, which are always an integer value.
- A
result set for each SELECT statement contained in the stored procedure
or any other stored procedures called by the stored procedure.
- A global cursor that can be referenced outside the stored procedure.
Stored
procedures assist in achieving a consistent implementation of logic
across applications. The SQL statements and logic needed to perform a
commonly performed task can be designed, coded, and tested once in a
stored procedure. Each application needing to perform that task can then
simply execute the stored procedure. Coding business logic into a
single stored procedure also offers a single point of control for
ensuring that business rules are correctly enforced.
Stored
procedures can also improve performance. Many tasks are implemented as a
series of SQL statements. Conditional logic applied to the results of
the first SQL statements determines which subsequent SQL statements are
executed. If these SQL statements and conditional logic are written into
a stored procedure, they become part of a single execution plan on the
server. The results do not have to be returned to the client to have the
conditional logic applied; all of the work is done on the server.
Functions :
Functions in programming languages are subroutines used to encapsulate frequently performed logic.
Any code that must perform the logic incorporated in a function can call the function
rather than having to repeat all of the function logic.
Functions are of supports two types of functions:
Built-in functions
Operate as defined in the Transact-SQL Reference and cannot be modified.
The functions can be referenced only in Transact-SQL statements using the syntax defined in
the Transact-SQL Reference.
User-defined functions
Allow you to define your own Transact-SQL functions using the CREATE FUNCTION statement.