SPs vs User Defined functions

Question

SPs vs User Defined functions

yaip

SSCrazy

Points: 2656
More actions
June 11, 2004 at 10:46 am

#87692

What is the difference between the two and when would you use which? Normally I've seen everything as SP.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cohen Old Hand Points: 321 More actions · Answer 1

Functions can return scalar data or tables and can be used in joins etc with a stored procedure. You'll need a stored procedure as your first level, but can encapsulate logic in functions that you call from your sprocs - eg if you had a function to split a csv string on the commas and it returned a table, you could then join on the returned table in a sp. another eg - you could have a function that applies complex logic to two columsn in a table and could then have a where clause like "where dbo.function(val1,val2) > 10"

Mark

Mark Cohen

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 2

SQL Server Books Online gives the following reasons to sometimes convert stored procedures to UDFs; and I think they give a good beginning on how the two are different from each other.

Rewriting Stored Procedures as Functions

This topic describes how to determine whether to rewrite existing stored procedure logic as user-defined functions. For example, if you want to invoke a stored procedure directly from a query, repackage the code as a user-defined function.

In general, if the stored procedure returns a (single) result set, define a table-valued function. If the stored procedure computes a scalar value, define a scalar function.

Criteria for Table-Valued Functions

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. This scenario can be handled with an inline table-valued function.
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. INSERT...EXEC statements can be written using table-valued functions. For example, consider the following sequence:
```
INSERT #temp EXEC sp_getresultsSELECT ...    FROM #temp, t1    WHERE ...
```
The sp_getresults stored procedure can be rewritten as a table-valued function, for example fn_results(), which means the preceding statements can be rewritten as:
```
SELECT ...    FROM fn_results(), t1    WHERE ...
```

The two really are fundamentally different.  One should only be used to perform functions, thus the name.  Stored Procedures on the other hand are encapsulated procedures that perform system or business logic through manipulation of data.

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.