Stored Procedures Vs. Functions

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.

Differences :

  1. 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.
  2. A UDF returns table variables, while a SPROC can’t return a table variable although it can create a table.
  3. Another significant difference between them is that UDFs can’t change the server environment or your operating system environment, while a SPROC can.
  4. 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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s