Wednesday, June 24, 2009

What is the differnce between store procedure and Functions (UDF)

A procedure or function is an object stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures and functions permit the caller to provide parameters that can be input only, output only, or input and output values. Procedures and functions let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language
1. Return Type: Functions should return atleast one output parameter. On the other hand it is not mandatory for the SP to return any value, but it could return one or more values limit is 1024.
2. Compiled Type: Functions are parsed and compile at runtime. On the other hand SP are stored in database as Compiled form.
3. State of the Database: Functions can not effects the state of the database, they cant invoke DDL (Database definition language) but can use DML (Data Manipulation Language). On the other hand SP can effect the state of database using DDL and use commit.
4. Invoke: Functions can be invoke from the SQL Statement e.g. Select. Whereas SP can not be invoke using Select Command.
5. Usage: Functions are generally use for computing values whereas SP use to apply business logics.

No comments:

Post a Comment

Locations of visitors to this page