Diffrence between function and StoreProcedure
Posted on May 23rd , 2009 by Admin
|
|
Both functions and stored procedures can be custom defined and part of any application.Functions, on the other hand, are designed to send their output to a query or T-SQL statement. For example, User Defined Functions (UDFs) can run an executable file from SQL SELECT or an action query,while Stored Procedures (SPROC) use EXECUTE or EXEC to run. Both are instantiated using CREATE FUNCTION.stored procedures are designed to return its output to the application. A UDF returns table variables,while SPROC can't return a table variable although it can create a table. Another significant difference between them is that UDFs can't change server environment or your operating system environment, while a SPROC can. Operationally, when T-SQL encounters an error the function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code (provided you've included error handling support). You'll also find that although a SPROC can be used in an XML FOR clause, a UDF cannot be. |
|
|
