anand_vanam's Avatar, Join Date: Apr 2010
Newbie Member
What is the difference between Functions and Stored Procedures?
1. Stored procedure supports deferred name resolution: but Function won’t support deferred name resolution.
2. Stored procedures return always an integer value by default zero. Where as function Scalar (single value or table or row set)
3. Stored procedures are precompiled execution plan but functions are not.
4. Functions in sql server can be one of two different types 1.scalar, 2. Table
Stored procedures are 2 types. 1. Regular sp, 2. Temporary sp
5. The result of the user defined function can be used with in a sql server statement. Where as the result of stored procedures can’t be used in sql server statements.
6. Stored procedure allows all functionality provided by a function but the reverse is not true.
satyedra pal's Avatar, Join Date: Mar 2010
stored procedure is a collection of SQL statements with an given name that's stored in the database in compiled format so that it can be used by more
programs again and again.Stored procedures improve performance by reducing network traffic and CPU load.It is created to performing a large operation like
more then one query execute simulteneously.Stored procedures can return result sets.You can use IF,WHILE,LOOP,ITERATION, CASE statements in store procedure

A user-defined function is a set of SQL statements you can call or set a function by name.user-defined function are similar to procedures,but a
function returns a value to the operation in which it is called.
user defined function use the folowing:
The SET clause in UPDATE statement
WHERE clause
The VALUES clause in INSERT statement
webdeveloperr's Avatar
Light Poster
nice information
khaleek_ahmad's Avatar, Join Date: Dec 2010
Newbie Member
Procedure can modify the state of Database but Function can not. Function can be called in query but Procedure can not.