Tuesday, November 6, 2012

Function Vs Stored Procedure

Both Function and Stored Procedure (SP) are pre compiled SQL statements but there some basic differences between them: 
  • Function must return a value whereas SP may or may not return a value.
  • A function can be used in a Join whereas SP can't be used in a Join. 
  • Functions can return a table whereas SP can create a table but can't return table.
  • XML and output parameters can't be passed to functions whereas it can be with SP. 
  • Transaction related statement can be handled in SP whereas it can't be in function.
  • We can have extended SP but not extended functions.
  • SP can call a function or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined SP can be called. Only extended/system defined procedures can be called.
  • SP can be called independently using exec keyword whereas function are called using select statements.
  • SP 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

No comments:

Post a Comment