In many instances you can accomplish the same task using either a stored procedure or a function. Both functions and stored procedures can be custom defined and part of any application.
- Functions, 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.
- A UDF returns table variables, while a SPROC can’t return a table variable although it can create a table.
- Another significant difference between them is that UDFs can’t change the server environment or your operating system environment, while a SPROC can.
- There’s quite a bit of debate about the performance benefits of UDFs Vs. SPROCs. You might be tempted to believe that stored procedures add more overhead to your server than a UDF.