Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Database (http://www.go4expert.com/articles/database/)
-   -   Difference between Stored procedures and User Defined functions[UDF] (http://www.go4expert.com/articles/difference-stored-procedures-user-t329/)

anand_vanam 6Apr2010 22:54

Re: Difference between Stored procedures and User Defined functions[UDF]
 
What is the difference between Functions and Stored Procedures?
Answer:
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 20Apr2010 13:28

Re: Difference between Stored procedures and User Defined functions[UDF]
 
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:
statement
The SET clause in UPDATE statement
WHERE clause
The VALUES clause in INSERT statement
START WITH, ORDER BY, and GROUP BY clauses

webdeveloperr 26May2010 15:59

Re: Difference between Stored procedures and User Defined functions[UDF]
 
nice information

khaleek_ahmad 31Dec2010 12:00

Re: Difference between Stored procedures and User Defined functions[UDF]
 
Procedure can modify the state of Database but Function can not. Function can be called in query but Procedure can not.


All times are GMT +5.5. The time now is 05:35.