Function vs. Stored Procedure in SQL Server

Asked at 2009-07-24 19:40:06Z
  • 5 Subscribers
  • 114 Views
471

I've been learning Functions and Stored Procedure for quite a while but I don't know why and when I should use a function or a stored procedure. They look same to me, maybe because I am kinda newbie about that.

Can some one tell me why?


17 answers in total

397
MyItchyChin Posted at 2009-07-24 19:42:14Z

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.

284
Bhaumik Patel Posted at 2012-10-15 17:00:55Z

The difference between SP and UDF is listed below:

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+
153
Chris J Posted at 2009-07-24 20:06:18Z

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).

44
Alex Martelli Posted at 2009-07-24 19:42:36Z

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!

36
Code Rider Posted at 2013-05-02 09:52:10Z

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.
15
OpenSource Posted at 2009-07-24 20:04:21Z

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.

12
Ankit Posted at 2014-02-28 10:55:26Z

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

8
Tigerjz32 Posted at 2015-03-13 19:55:56Z

Stored Procedures are used as scripts. They run series of commands for you and you can schedule them to run at certain times.

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

6
Arnkrishn Posted at 2009-07-24 19:54:50Z

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

  1. Stored procedures can't return a table variable where as function can do that.

  2. You can use stored procedures to alter the server environment parameters where as using functions you can't.

cheers

4
Achilles Posted at 2009-12-09 20:27:49Z

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.

  1. Never use a function to return a result-set to an external code (like ADO.Net)
  2. 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

3
JaiSankarN Posted at 2014-04-29 11:56:17Z

Stored procedure

  • It is like a miniture program in SQL Server
  • It 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.
  • (Storedprocedures can also 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

  • They can not be used to update, delete, or add records to the database.
  • They simply return a single value or a table value.
  • They can only be use to select records. However,they 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 simply our 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 with JOIN those tables together for EACH line returned in the result set. So though they can be helpful in simpling some logic, they can also be a performance bottleneck if they're not used properly
  • Returns the values using OUT parameter
  • Not supports transactions
1
Andrew Posted at 2013-09-16 20:28:10Z

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.

1
kombsh Posted at 2013-10-15 12:19:13Z
  • 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.
0
Nick Kahn Posted at 2012-07-21 19:33:57Z
  • 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.

0
user2023861 Posted at 2016-05-31 17:57:26Z

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)

-2
Z.I.J Posted at 2014-12-19 05:32:17Z

Differences between Stored Procedure and User Defined Function

enter image description here

-2
Jason Clark Posted at 2015-12-17 07:19:06Z

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:

  1. System defined

  2. 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:

  1. System Stored Procedures
  2. User Defined Procedures

Answer this questsion