Function vs. Stored Procedure in SQL Server

Question

When should I use a function rather than a stored procedure in SQL, and vice versa? What is the purpose of each?

http://venkatsqlinterview.blogspot.com/2011/05/what-is-difference-between-user-defined.html — Freelancer, May 24 '13 at 09:21
http://wiki.answers.com/Q/What_is_difference_between_function_and_stored_procedure_in_sql_server — Freelancer, May 24 '13 at 09:22
http://searchsqlserver.techtarget.com/tip/Stored-procedures-vs-functions — Freelancer, May 24 '13 at 09:22
worth mentioning that SP can create transactions while function not — Hesham Yassin, Dec 09 '20 at 07:57

score 824 · Accepted Answer · edited Jun 18 '20 at 14:46

824

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).

A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.

A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.

edited Jun 18 '20 at 14:46

Vanity Slug - codidact.com

1,347
1
16
31

answered Jul 24 '09 at 19:42

MyItchyChin

13,733
1
24
44

23

Basically no DML is allowed ? – david blaine Apr 25 '13 at 07:53
31

In fact you can have INSERT, UPDATE, and DELETE statements in a function, for modifying local table variables. – Ani Mar 27 '14 at 07:39
18

@Ani - You can instantiate and modify any number of local variables with in a function however you cannot modify anything outside of the scope of the function. – MyItchyChin Apr 03 '14 at 15:28
45

@SeanKAnderson function "must have at least one parameter" is not true. – liang Jun 17 '15 at 08:02
3

Note: abusing of inline functions can painfully slow down your queries – jean Sep 01 '15 at 18:42
@SeanKAnderson Are stored functions required to be idempotent? – alex May 26 '16 at 13:16
@alex - No, they're not. – MyItchyChin May 26 '16 at 20:39
Functions in Tsql are more like `Pure Functions`. – Abdul Samad Sep 11 '17 at 10:33
Unlike stored procedures, the functions are compiled every time they are called. Hence the query execution plan will be generated every time, which makes them less effective. – turbo88 Mar 07 '18 at 23:57
@turbo88 Are you sure about that ? – Bimal Das Aug 29 '18 at 04:04
Wrong statement: " Functions are not allowed to change anything, must have at least one parameter, and they must return a value." Function can be created without parameters. @SeanAnderson – Bimal Das Aug 29 '18 at 04:07
1

@SeanAnderson. SPs do return a value. They can also RAISERRROR. – PerformanceDBA Dec 26 '19 at 09:27
1

Also functions cannot PRINT. I guess printing is considered performing "permanent environmental changes" :) – Reversed Engineer Jan 24 '20 at 06:02
A side effect of this condition: Stored procedures may call EXEC/SP_EXECUTESQL to execute arbitrary/dynamic sql. Function are not allowed to that - which is quite clear since that sql may lead to side effects. – Grimm Jun 23 '20 at 15:12
You can DML in a function. – Mohammad Jihad Helal Mar 26 '21 at 12:46
Functions have no side effects whereas procedures do. – Peter Chaula May 10 '23 at 04:23

score 780 · Answer 2 · edited Jan 10 '23 at 00:02

780

Here's a table summarizing the differences:

	Stored Procedure	Function
Returns	Zero or more values	A single value (which may be a scalar or a table)
Can use transaction?	Yes	No
Can output to parameters?	Yes	No
Can call each other?	Can call a function	Cannot call a stored procedure
Usable in SELECT, WHERE and HAVING statements?	No	Yes
Supports exception handling (via try/catch)?	Yes	No

edited Jan 10 '23 at 00:02

Karl Knechtel

62,466
11
102
153

answered Oct 15 '12 at 17:00

Bhaumik Patel

15,176
5
30
33

28

Functions must return one value, or a set. – Rafareino Jul 27 '15 at 18:12
15

This came 3 years later but should be on top because it's both readable and extensive. – DanteTheSmith Sep 27 '17 at 13:03
1

SP may use both temp tables and table variables whereas UDF may only use table variables. Table variables in turn may not use indexes. UDF can be called in a CROSS APPLY unlike SP – Ludovic Aubert Aug 08 '19 at 09:11

score 202 · Answer 3 · edited Sep 07 '10 at 13:56

Functions and stored procedures serve separate purposes. Although it's not the best analogy, functions can be viewed literally as any other function you'd use in any programming language, but stored procs are more like individual programs or a batch script.

Functions normally have an output and optionally inputs. The output can then be used as the input to another function (a SQL Server built-in such as DATEDIFF, LEN, etc) or as a predicate to a SQL Query - e.g., SELECT a, b, dbo.MyFunction(c) FROM table or SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

Stored procs are used to bind SQL queries together in a transaction, and interface with the outside world. Frameworks such as ADO.NET, etc. can't call a function directly, but they can call a stored proc directly.

Functions do have a hidden danger though: they can be misused and cause rather nasty performance issues: consider this query:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Where MyFunction is declared as:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

What happens here is that the function MyFunction is called for every row in the table MyTable. If MyTable has 1000 rows, then that's another 1000 ad-hoc queries against the database. Similarly, if the function is called when specified in the column spec, then the function will be called for each row returned by the SELECT.

So you do need to be careful writing functions. If you do SELECT from a table in a function, you need to ask yourself whether it can be better performed with a JOIN in the parent stored proc or some other SQL construct (such as CASE ... WHEN ... ELSE ... END).

Can you please elaborate on "Frameworks such as ADO.NET, etc. can't call a function directly"? I've executed functions with ADO.NET data providers with no issues. — Ian Kemp, Feb 20 '12 at 09:00
You have to call a function through some SELECT statement - a function can't be called as an independent piece of code in its own right - it has to be called as part of some larger SQL statement, even if that SQL statement is nothing more than `SELECT * from dbo.MyTableValuedFunction()`. Sprocs, on the otherhand, can be called directly with ADO.NET by setting `SqlCommand.CommandType` to `CommandType.StoredProcedure`. — Chris J, Feb 20 '12 at 10:37
A user-defined function can be called via the `EXECUTE` statement. See Microsoft's article [Execute User-defined Functions](https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/execute-user-defined-functions?view=sql-server-ver15). — DavidRR, Apr 07 '22 at 19:30

score 69 · Answer 4 · edited May 26 '16 at 14:26

Differences between stored procedures and user-defined functions:

Stored procedures cannot be used in Select statements.
Stored procedures support Deferred Name Resolution.
Stored procedures are generally used for performing business logic.
Stored procedures can return any datatype.
Stored procedures can accept greater numbers of input parameter than user defined functions. Stored procedures can have up to 21,000 input parameters.
Stored procedures can execute Dynamic SQL.
Stored procedures support error handling.
Non-deterministic functions can be used in stored procedures.

User-defined functions can be used in Select statements.
User-defined functions do not support Deferred Name Resolution.
User-defined functions are generally used for computations.
User-defined functions should return a value.
User-defined functions cannot return Images.
User-defined functions accept smaller numbers of input parameters than stored procedures. UDFs can have up to 1,023 input parameters.
Temporary tables cannot be used in user-defined functions.
User-defined functions cannot execute Dynamic SQL.
User-defined functions do not support error handling. RAISEERROR OR @@ERROR are not allowed in UDFs.
Non-deterministic functions cannot be used in UDFs. For example, GETDATE() cannot be used in UDFs.

To quote @curiousBoy below re. another un-credited answer (by @Ankit) (<-- see how I did that? ;) ): "You should have given the source reference. This is from (https://blogs.msdn.microsoft.com/pradeepsvs/2014/10/08/difference-between-a-stored-procedure-and-function/). Please respect the work that others do!" — Tom, Mar 24 '17 at 18:39
This Blogs had been written since Oct 8 2014 and this answer had been written since May 2, 2013 @Tom — Kumar Manish, Mar 27 '17 at 09:08
@Code Rider: Ah, my apologies! Can't believe I didn't notice that! So, the blog copied you (or someone else who did) without credit? — Tom, Apr 05 '17 at 06:37
`GETDATE()` can be used in a Function. The pivot on *Non-deterministic* is not a good one. — PerformanceDBA, Dec 26 '19 at 09:34

score 66 · Answer 5 · edited Dec 02 '21 at 12:11

STORE PROCEDURE	FUNCTION (USER DEFINED FUNCTION)
Procedure can return 0, single or multiple values	Function can return only single value
Procedure can have input, output parameters	Function can have only input parameters
Procedure cannot be called from a function	Functions can be called from procedure
Procedure allows select as well as DML statement in it	Function allows only select statement in it
Exception can be handled by try-catch block in a procedure	Try-catch block cannot be used in a function
We can go for transaction management in procedure	We can not go for transaction management in function
Procedure cannot be utilized in a select statement	Function can be embedded in a select statement
Procedure can affect the state of database means it can perform CRUD operation on database	Function can not affect the state of database means it can not perform CRUD operation on database
Procedure can use temporary tables	Function can not use temporary tables
Procedure can alter the server environment parameters	Function can not alter the environment parameters
Procedure can use when we want instead is to group a possibly- complex set of SQL statements	Function can use when we want to compute and return a value for use in other SQL statements

I think it would be better to integrate this information into Braumik Patel's answer, rather than leaving it separate. — Karl Knechtel, Jan 10 '23 at 00:03

score 59 · Answer 6 · answered Jul 24 '09 at 19:42

59

Write a user-defined function when you want to compute and return a value for use in other SQL statements; write a stored procedure when you want instead is to group a possibly-complex set of SQL statements. These are two pretty different use cases, after all!

answered Jul 24 '09 at 19:42

Alex Martelli

854,459
170
1,222
1,395

19

there are different types of user-defined functions. Scalar ones return only values; other types retrun result sets. – A-K Sep 07 '10 at 15:31

score 28 · Answer 7 · edited Aug 24 '20 at 05:20

Basic Difference

Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).

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

Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..

Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

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

Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

We can go for Transaction Management in Procedure whereas we can't go in Function.

source

You should have given the source reference. This is from http://www.dotnet-tricks.com/Tutorial/sqlserver/7EDL150912-Difference-between-Stored-Procedure-and-Function-in-SQL-Server.html . Please respect the work that others do! — curiousBoy, Aug 11 '14 at 17:39
It is not a reason to not give a source reference. You can mention at the end of it! — curiousBoy, Aug 12 '14 at 16:51
Re. "Function must return a value but in Stored Procedure it is optional....": I would clarify that: "Functions **must** return one and only one value (which must be done via the `Returns` keyword and must be a Scalar or Table type), but Stored Procedures can **optionally** return: a) 1 `Int` type Result Code via the `Return` Statement and/or b) 1+ Parameters (incl. `Cursor` type) via the `Output` keyword and/or c) 1+ Row Sets via `Select` Statements. If only 1 Row Set is returned, it can be used as the "execute_statement" argument of an "Insert Into" Statement." — Tom, Mar 24 '17 at 18:58

score 22 · Answer 8 · edited Jul 09 '13 at 20:54

a User Defined Function is an important tool available to a sql server programmer. You can use it inline in a SQL statement like so

SELECT a, lookupValue(b), c FROM customers

where lookupValue will be an UDF. This kind of functionality is not possible when using a stored procedure. At the same time you cannot do certain things inside a UDF. The basic thing to remember here is that UDF's:

cannot create permanent changes
cannot change data

a stored procedure can do those things.

For me the inline usage of a UDF is the most important usage of a UDF.

Tigerjz32 · Answer 9 · 2020-04-07T05:37:37.013

18

Stored Procedures are used as scripts. They run a series of commands for you and you can schedule them to run at certain times. Usually runs multiples DML statements like INSERT, UPDATE, DELETE, etc. or even SELECT.

Functions are used as methods. You pass it something and it returns a result. Should be small and fast - does it on the fly. Usually used in a SELECT statement.

edited Apr 07 '20 at 05:37

answered Mar 13 '15 at 19:55

Tigerjz32

4,324
4
26
34

2

This is a good summary of the two, quick and dirty way to think of them. – Eric Bishard Dec 11 '15 at 09:15
3

Indeed a good summary. Other answers focus on the theoretical difference of the two, while still leaving me unsure of when to use which one in practice. – jf328 Dec 21 '16 at 15:02

score 8 · Answer 10 · answered Dec 09 '09 at 20:27

8

SQL Server functions, like cursors, are meant to be used as your last weapon! They do have performance issues and therefore using a table-valued function should be avoided as much as possible. Talking about performance is talking about a table with more than 1,000,000 records hosted on a server on a middle-class hardware; otherwise you don't need to worry about the performance hit caused by the functions.

Never use a function to return a result-set to an external code (like ADO.Net)
Use views/stored procs combination as much as possible. you can recover from future grow-performance issues using the suggestions DTA (Database Tuning Adviser) would give you (like indexed views and statistics) --sometimes!

for further reference see: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

answered Dec 09 '09 at 20:27

Achilles

1,554
1
28
36

1

Thanks. Wrote a function today to call within a query to populate values for one column. Execute ran for over 3-minutes before I stopped it. Figured out a JOIN way to do it. Execute finished in 15 seconds. (Data set was 3456 rows). Big performance diff. – VISQL Jul 10 '12 at 18:30
edit: Execute finishes between 15 and 50 seconds depending on which column I "ORDER BY"(Data set was 3456 rows). Big performance difference. – VISQL Jul 10 '12 at 18:43
The performance difference may have roots in different types of those columns you're ordering the result by. SQL Server works much better with numbers than character data. You can use DTA on that 50secs query and see if it can come up with some sort of stats/index suggestions to make the query run a bit faster. – Achilles Jul 11 '12 at 18:05
1

I'm not sure enough evidence has been provided to say that it should be a last resort. You can think of a function as a parameterized view which can be further operated on. Eg, you want to join customers to orders, but only for michigan. You create a customerOrders(@StateCode) function which will only join a single state's worth of customer's. Then, I can further operate on this set as Select FirstName, LastName, OrderTotal, StoreName From CustomerOrders('MI') INNER JOIN Stores ON Stores.StoreID = Orders.StoreID WHERE OrderTotal > 100; This would be a pain with SPs as you must temp copy. – MPavlak Aug 09 '12 at 19:06
How many records do you have in that table? If your hardware handles it properly, you won't need to worry about choosing weapons. A spoon can do the job when it's hard enough to break a sword; this hardness is called HARDWARE! – Achilles Aug 11 '12 at 08:00
...would your advise stand when returning data to integration services - or because it is part of the MS stack will the performance issues be minimal? – whytheq May 13 '13 at 11:33
Scalar functions and multi-statement table-valued functions will cause row-by-row processing and can often have disastrous performance ramifications. However, inline table-valued functions are basically views and when used properly do not hinder performance. They can serve a valuable role in making database code more modular. – Riley Major Feb 28 '14 at 22:03

score 8 · Answer 11 · edited Mar 28 '18 at 16:32

Stored procedure:

Is like a miniature program in SQL Server.
Can be as simple as a select statement, or as complex as a long script that adds, deletes, updates, and/or reads data from multiple tables in a database.
(Can implement loops and cursors, which both allow you to work with smaller results or row by row operations on data.)
Should be called using EXEC or EXECUTE statement.
Returns table variables, but we can't use OUT parameter.
Supports transactions.

Function:

Can not be used to update, delete, or add records to the database.
Simply returns a single value or a table value.
Can only be used to select records. However, it can be called very easily from within standard SQL, such as:
```
SELECT dbo.functionname('Parameter1')
```
or
```
SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
```
For simple reusable select operations, functions can simplify code. Just be wary of using JOIN clauses in your functions. If your function has a JOIN clause and you call it from another select statement that returns multiple results, that function call will JOIN those tables together for each line returned in the result set. So though they can be helpful in simplifying some logic, they can also be a performance bottleneck if they're not used properly.
Returns the values using OUT parameter.
Does not support transactions.

score 6 · Answer 12 · answered Jul 24 '09 at 19:54

To decide on when to use what the following points might help-

Stored procedures can't return a table variable where as function can do that.
You can use stored procedures to alter the server environment parameters where as using functions you can't.

cheers

score 4 · Answer 13 · answered Sep 16 '13 at 20:28

Start with functions that return a single value. The nice thing is you can put frequently used code into a function and return them as a column in a result set.

Then, you might use a function for a parameterized list of cities. dbo.GetCitiesIn("NY") That returns a table that can be used as a join.

It's a way of organizing code. Knowing when something is reusable and when it is a waste of time is something only gained through trial and error and experience.

Also, functions are a good idea in SQL Server. They are faster and can be quite powerful. Inline and direct selects. Careful not to overuse.

score 4 · Answer 14 · answered May 31 '16 at 17:57

Here's a practical reason to prefer functions over stored procedures. If you have a stored procedure that needs the results of another stored procedure, you have to use an insert-exec statement. This means that you have to create a temp table and use an exec statement to insert the results of the stored procedure into the temp table. It's messy. One problem with this is that insert-execs cannot be nested.

If you're stuck with stored procedures that call other stored procedures, you may run into this. If the nested stored procedure simply returns a dataset, it can be replaced with a table-valued function and you'll no longer get this error.

(this is yet another reason we should keep business logic out of the database)

score 3 · Answer 15 · answered Jan 18 '19 at 06:52

I realize this is a very old question, but I don't see one crucial aspect mentioned in any of the answers: inlining into query plan.

Functions can be...

Scalar:

CREATE FUNCTION ... RETURNS scalar_type AS BEGIN ... END
Multi-statement table-valued:

CREATE FUNCTION ... RETURNS @r TABLE(...) AS BEGIN ... END
Inline table-valued:

CREATE FUNCTION ... RETURNS TABLE AS RETURN SELECT ...

The third kind (inline table-valued) are treated by the query optimizer essentially as (parametrized) views, which means that referencing the function from your query is similar to copy-pasting the function's SQL body (without actually copy-pasting), leading to the following benefits:

The query planner can optimize the inline function's execution just as it would any other sub-query (e.g. eliminate unused columns, push predicates down, pick different JOIN strategies etc.).
Combining several inline function doesn't require materializing the result from the first one before feeding it to the next.

The above can lead to potentially significant performance savings, especially when combining multiple levels of functions.

NOTE: Looks like SQL Server 2019 will introduce some form of scalar function inlining as well.

score 2 · Answer 16 · edited Dec 11 '13 at 16:35

It is mandatory for Function to return a value while it is not for stored procedure.
Select statements only accepted in UDF while DML statements not required.
Stored procedure accepts any statements as well as DML statements.
UDF only allows inputs and not outputs.
Stored procedure allows for both inputs and outputs.
Catch blocks cannot be used in UDF but can be used in stored procedure.
No transactions allowed in functions in UDF but in stored procedure they are allowed.
Only table variables can be used in UDF and not temporary tables.
Stored procedure allows for both table variables and temporary tables.
UDF does not allow stored procedures to be called from functions while stored procedures allow calling of functions.
UDF is used in join clause while stored procedures cannot be used in join clause.
Stored procedure will always allow for return to zero. UDF, on the contrary, has values that must come - back to a predetermined point.

score 1 · Answer 17 · edited Jan 16 '14 at 14:25

Functions can be used in a select statement where as procedures cannot.
Stored procedure takes both input and output parameters but Functions takes only input parameters.
Functions cannot return values of type text, ntext, image & timestamps where as procedures can.
Functions can be used as user defined datatypes in create table but procedures cannot.

***Eg:-create table <tablename>(name varchar(10),salary getsal(name))

Here getsal is a user defined function which returns a salary type, when table is created no storage is allotted for salary type, and getsal function is also not executed, But when we are fetching some values from this table, getsal function get’s executed and the return Type is returned as the result set.

Gabriele Franco · Answer 18 · 2020-07-16T21:30:56.197

Generally using stored procedures is better for perfomances. For example in previous versions of SQL Server if you put the function in JOIN condition the cardinality estimate is 1 (before SQL 2012) and 100 (after SQL 2012 and before of SQL 2017) and the engine can generate a bad execution plan.

Also if you put it in WHERE clause the SQL Engine can generate a bad execution plan.

With SQL 2017 Microsoft introduced the feature called interleaved execution in order to produce a more accurate estimate but the stored procedure remains the best solution.

For more details look the following article of Joe Sack https://techcommunity.microsoft.com/t5/sql-server/introducing-interleaved-execution-for-multi-statement-table/ba-p/385417

score -3 · Answer 19 · answered Dec 17 '15 at 07:19

In SQL Server, functions and stored procedure are two different types of entities.

Function: In SQL Server database, the functions are used to perform some actions and the action returns a result immediately. Functions are two types:

System defined
User defined

Stored Procedures: In SQL Server, the stored procedures are stored in server and it can be return zero, single and multiple values. Stored Procedures are two types:

System Stored Procedures
User Defined Procedures

Function vs. Stored Procedure in SQL Server

19 Answers19

Linked

Related