Difference between Stored Procedure and User Defined Function (UDF)
UDF (User Defined Function)
- User Defined Functions (UDFs) can to executed in the SQL statements anywhere in the WHERE/HAVING/SELECT section.
- UDFs that return tables can be treated as another dataset. This can be used in JOINs with other tables.
- A UDF returns table variables.
- UDFs can't change the server environment or your operating system environment.
- Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
- UDF's doesn't support error handling. TRY - CATCH cannot be used in User defined functions.
Stored Procedure
- Stored procedures can not be used in WHERE/HAVING/SELECT section, they have to use EXECUTE or EXEC to run.
- SPROC can't return a table variable although it can create a table.
- SPROCs can change server or operating system environment variables by using the system store procedure. (eg: Change the values for job execution (enable or disable)
- Stored Procedure can have a function in it, while a function cannot have a Stored Procedure in it.
No comments:
Post a Comment