Stored Procedure Vs Function in SQL Server

Guys,

Let's discuss some points about Stored Procedure & Functions in SQL Server.


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 return 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 stored 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.


8. We can have extended stored procedures but not extended functions.


9. A function can be used in a Join whereas Stored proc can't be used in a Join.

Hope this is helpful.


Sudhir Chawla