Functions vs Stored Procedures

Let's say I have to implement a piece of T-SQL code that must return a table as result. I can implement a table-valued function or else a stored procedure that returns a set of rows. What should I use?

In short, what I want to know is:

Which are the main differences between functions and stored procedures? What considerations do I have to take into account for using one or the other?

If you're likely to want to combine the result of this piece of code with other tables, then obviously a table-valued function will allow you to compose the results in a single SELECT statement.

Generally, there's a hierarchy (View < TV Function < Stored Proc). You can do more in each one, but the ability to compose the outputs, and for the optimizer to get really involved decreases as the functionality increases.

So use whichever one minimally allows you to express your desired result.

Functions must be deterministic, and cannot be used to make changes to the database, whereas stored procedures allow you to do inserts and updates, etc.

You should limit your use of functions, since they pose a huge scalability problem for big, complex queries. They become sort of a "black box" for the query optimizer, and you'll see enormous differences in performance between using functions and simply inserting the code into a query.

But they are definitely useful for table-valued returns in very specific cases.

If you need to parse a comma-delimited list, to simulate passing an array to a procedure, a function can turn the list into a table for you. This is common practice with Sql Server 2005, since we can't pass in tables to stored procedures yet (we can with 2008).

From the docs:

> If a stored procedure meets the following criteria, it is a good candidate for being rewritten as a table-valued function: > > * The logic is expressible in a single SELECT statement but is a stored procedure, rather than a view, only because of the need for parameters. > > * The stored procedure does not perform update operations, except to table variables. > > * There is no need for dynamic EXECUTE statements. > > * The stored procedure returns one result set. > > * The primary purpose of the stored procedure is to build intermediate results that are to be loaded into a temporary table, which is then queried in a SELECT statement.

I am going to write few interesting differences between stored procedures and functions.

• We can use functions in select queries but we cannot use stored procedures in select queries.

• We cannot use non deterministic functions in Functions but we can use non deterministic functions in stored procedures. Now question comes up, what is non deterministic function.. Ans is:-

  >A non deterministic function is that function which returns different outputs for same input values at different time, like getdate(). It always returns different value whenever it is run.

Exception:- >Earlier versions of sql server prior to sql 2000 do not allow to use getdate() function in user defined functions, but version 2005 and onward allows us to use getdate() function within a user defined function.

>Newid() is another example of non deterministic function but cannot be used in user defined functions but we can use it in stored procedure.

• We can use DML(insert, update, delete) statements within a stored procedure but we cannot use DML statements in functions on physical tables or permanent tables. If we want to do DML operation in functions we can do it over table variables not on permanent tables.

• We cannot use error handling within function but we can do error handling in stored procedures.

1. Procedure can return zero or n values whereas function can return one value which is mandatory.

2. Procedures can have input/output parameters for it whereas functions can have only input parameters.

3. Procedure allows select as well as DML statement in it whereas function allows only select statement in it.

4. Functions can be called from procedure whereas procedures cannot be called from function.

5. Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6. We can go for transaction management in procedure whereas we can't go in function.

7. Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

8. UDF (User Defined function) can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas stored procedures cannot be.

9. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

10. Inline UDFs can be though of as views that take parameters and can be used in JOINs and other rowset operations.

If you have a function you could use it as a part of your SQL statement, for example

SELECT function_name(field1) FROM table

It does not work this way for the stored procedures.

I ran some tests with a long running bit of logic, with the same bit of code (a long SELECT statement) running in both a Table Valued Function and a Stored Procedure, and a straight EXEC/SELECT, and each performed identically.

In my opinion always use a Table Valued Function rather than a stored procedure to return a result set, as it makes logic much easier and readable in queries that subsequently join to them, and enables you to reuse the same logic. To avoid too much of a performance hit, I often use "optional" parameters (i.e. you can pass NULL to them) to enable the function to return the result set to be quicker, e.g.:

CREATE FUNCTION dbo.getSitePermissions(@RegionID int, @optPersonID int, optSiteID int)
AS
RETURN 
    SELECT DISTINCT SiteID, PersonID
    FROM dbo.SiteViewPermissions
    WHERE (@optPersonID IS NULL OR @optPersonID = PersonID)
    AND (@optSiteID IS NULL OR @optSiteID = SiteID)
    AND @RegionID = RegionID

This way you can use this function for many different situations, and don't take a huge performance hit. I believe this is more efficient than filtering afterwards:

SELECT * FROM dbo.getSitePermissions(@RegionID) WHERE SiteID = 1

I have used this technique in several functions, sometimes with a long list of "optional" parameters of this type.

I personally use table valued functions when all I am returning is a single table with no affects. Basically I treat them like parameterized views.

If I need multiple recordsets returned or if there will be values updated in tables, I use a stored procedure.

My 2 cents

As mentioned above, functions are more readable/composable/self documenting, but are less performant in general, and can be seriously less performant if you get carried away with them in joins such as

SELECT *
FROM dbo.tvfVeryLargeResultset1(@myVar1) tvf1
INNER JOIN dbo.tvfVeryLargeResultset1(@myVar2) tvf2
    ON (tvf1.JoinId = tvf2.JoinId)

Often, you just have to accept the redundancy of code that a tvf could eliminate (at a unacceptable performance cost.)

One other point I haven't yet seen mentioned is that you can't use database state-changing temp tables inside of a multi-statement tvf. The most functionally equivalent mechanism to a temp table is the non-state changing, in memory table variable, and for large datasets, a temp table will likely be more performant than a table variable. (Other alternatives include dynamic tables & common table valued expressions, but at some level of complexity, these cease to be a good option IMO.)

It depends :) If you want to use the table-valued result in another procedure, you're better of using a TableValued Function. If the results is for a client, the stored proc is usualy the better way to go.

I would perfromance test both. It is likely the sp approach or a derived table would be significantly faster than a function and if so that approach should be used. In general I avoid functions becasue they can be performance hogs.

Stored procedures are pre compiled queries which executes faster and saves from the sql injections. They can return 0 or N values. We can perform DML operations inside the stored procedures. We can use functions inside the procedures and can use functions in the select query. Functions are used to return any value and DML operations not possible in functions. functions are of two types scalar and tabled-valued. scalar function returns a single value, tabled-valued function used to returns rows of tables.