Amir Mirkamali

امیر میرکمالی

Stored procedure Vs User Functions

  1. in functions u cant use DDL , DML smts (like Insert and Update) which is possible in SPs
  2. U can use function output in select smts (like Select fnTest(), a from test a)
  3. U can use the function output in joins (like test a LEFT OUTER JOIN fnTest() b )
  4. Sp can retun multiple resultset, function can return only one resultset
  5. Function can accept less parmeter than procedure
  6. Function can not return XML output
  7. Stored Procedures are basically compiled which results in faster execution.
  8. Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.
  9. Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
  10. Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures
Comments are closed