Thursday, October 11, 2007

Sproc Vs UDF

I got this from SQL server magazine. Yeah.. I know this is very basic thing but honestly speaking, I did not know about point 3, so this is note for myself:
  1. A UDF must return a value-a single result set. A stored procedure can return a value-or even multiple result sets-but doesn't have to.
  2. You can use a UDF directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can't use a stored procedure in a SELECT statement.
  3. A UDF can't use a nondeterministic function such as GETDATE(), NEWID(), or RAND(), whereas a stored procedure can use such functions. A nondeterministic function is one that can return a different result given the same input parameters.
  4. A UDF can't change server environment variables; a stored procedure can.
  5. A UDF always stops execution of T-SQL code when an error occurs, whereas a stored procedure continues to the next instruction if you've used proper error handling code.
  6. A UDF can not execute DDL statements. It can perform insert, update and delete operations on only temporary tables not on permanent tables.
  7. You can't have trannsactions in UDF
  8. You can call stored procedures with in a stored procedure but not with in a function. Ofcourse you can call functions with in a function.

No comments: