Difference between stored procedure and Functions

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/
Ebook Download
View all
Learn
View all