- 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.
- 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.
- 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.
- A UDF can't change server environment variables; a stored procedure can.
- 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.
- A UDF can not execute DDL statements. It can perform insert, update and delete operations on only temporary tables not on permanent tables.
- You can't have trannsactions in UDF
- 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.
Blogs on .NET and LAMP Technologies
There's often more than one correct thing.
There's often more than one right thing.
There's often more than one obvious thing.
--Larry Wall
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:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment