Difference between stored procedure and Functions

By Vishal Nayan Apr 30, 2011
What is difference between Stored procedure and Functions? Stored procedures:

What is difference between Stored procedure and Functions?

 

Stored procedures:

1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won't return Table Variables
4) you cannot join SP
5) Can be used to change server configuration.
6) Can be used with XML FOR Clause
7) can have transaction within SP

8) Procedures can be used for performing business logic

9) Sp takes input, output parameters,
10) Sp cannot be called directly into DML statements 
11) PROCEDURE may return one or more values through parameters or may not return at all
12) Procedure can return multiple values (max 1024).
13) Stored procedure returns always integer value by default zero.
14)  Stored procedure is precompiled execution plan 
15) Procedure cannot be used in SQL queries

 

Functions

1) Can be used with Select statement
2) Not returning output parameter but returns Table variables
3) We can join UDF
4) Cannot be used to change server configuration.
5) Cannot be used with XML FOR clause
6) cannot have transaction within function

7) Functions are used for computations
8) Function takes only input parameters.
9) Functions can be called directly into DML statements.
10) A FUNCTION is always returns a value using the return statement
11) A Function returns 1 value only.
12) Whereas function returns type could be scalar or table or table values

13) A function can call directly by SQL statement like select func_name from dual

14) A Function can be used in the SQL Queries


http://vishalnayan.wordpress.com/
Vishal Nayan

Vishal is a seasoned professional with hand on experience on Microsoft technologies. He is also a part-time trainer on framework , WPF ,WCF , Silverlight and is active in Mauritius.

COMMENT USING

PREMIUM SPONSORS

Hire Mobile & Web Developer on demand. 100% satisfaction. Try for 1 week or Money Back. Local and remote developers available all over USA.

Latest Blogs