Tuesday, January 3, 2012

1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You 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

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


1) functions are used for computations where as procedures 
can be used for performing business logic
2) functions MUST return a value, procedures need not be.
3) you can have DML(insert, update, delete) statements in a 
function. But, you cannot call such a function in a SQL 
query..eg: suppose, if u have a function that is updating a 
table.. you can't call that function in any sql query.- 
select myFunction(field) from sometable; will throw error.
4) function parameters are always IN, no OUT is possible

a. A FUNCTION is always returns a value using the return 
statement. A  PROCEDURE may return one or more values 
through parameters or may not return at all.
b. Functions are normally used for computations where as 
procedures are normally used for executing business logic.
c. A Function returns 1 value only. Procedure can return 
multiple values (max 1024).
d. Stored procedure returns always integer value by default 
zero. Whereas function returns type could be scalar or 
table or table values
e. Stored procedure is precompiled execution plan where as 
functions are not.
f. A function can call directly by SQL statement like 
select func_name from dual while procedure cannot.
g.Stored procedure has the security and reduces the network 
traffic and also we can call stored procedure in any no. of 
applications at a time.
h. A Function can be used in the SQL Queries while a 
procedure cannot be used in SQL queries .that cause a major 
difference b/w function and procedures.

Although both functions and sp's are prcomiled sql statements there exists some differences between them.

1. Functions must return a value(scalar,inline table or multi statement table) whereas stored proc may or may not retun a value.
2.Functions can return a table whereas stored procs can create a table but can't return table.
3. Stored procs can be called independently using exec keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6.transaction related statement can be handled in sp whereas it can't be in function.
7. stored procedures can call a funtion or another sstored proc similarly a function can call another function and a stored proc.The catch with function is that no user defined stored proc can be called.Only extended/system defined procs can be called.

No comments:

Post a Comment