Function vs. Stored Procedure in SQL Server

Question

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?

Answer 1

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 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.