Differences between User Defined Function(s) and Stored Procedure(s)

Hello!!! Hope the reader is in good state while reading this article!!!!



T-SQL FUNCTIONS:

A block of T-SQL code , that returns a single value / result set (Scalar Value, Table) after processing input (User Entered or Default value).

STORED PROCEDURE:

A common name given to a block of T-SQL code, which when executed, provides output to the user.

Aha!! The above two definitions look similar to each other and on a vigilant look, they both mean the same. Then what could be the difference. Are they really the same or is there any difference between them??

Yes, there are a lot of differences between a T-SQL function and a T-SQL Stored Procedure.

The following list provides you with most of the them.

1. Functions will always return a single value / result set (if the function is table-valued / inline). Whereas a stored procedure may (0....n) or may not return any value / result set to the user.

2. Functions are compiled only once in their entire lifespan, whereas you have an option of recompiling a stored procedure against each request (this may degrade your server performance).

3. Functions will only have input parameters, but no output parameters. Whereas a stored procedure can either have input parameters, output parameters or both.

4. During function execution, the user has to specify the names and values (Scalar Value / DEFAULT keyword) of all the input parameters present in its definition, even if the input parameter posses default value. If even a single input parameter is missed, then the server throws an error. This is not applicable to Stored Procedures.

Example:

*   If suppose a function contains 4 input parameters and they all have a default value, and while calling that function, the user does not specifies the third input parameter, then the Server, instead of taking the default value of the 3 parameter from the definition, will throw an error and immediately halts the function execution.
*  Whereas, if a stored procedure is having an input parameter with some default value, and a user while calling the sp does not specify that parameter, then the server takes the default value of that parameter from its definition and continues executing the stored procedure.

5. Functions cannot change the data present in permanent tables, it only supports DSL (Data Selection Language). Whereas you can perform all DML operations using a stored procedure.(DSL + DML).

6. Functions does not support TCL (Transaction Control Language) and EHL (Error Handling Language) . TCL includes (BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK) and EHL includes (TRY...CATCH).Whereas Stored Procedures provides complete support to TCL and EHL.

7. A user cannot use non-deterministic functions in User Defined functions, except for GETDATE() (this method got included from SQL Server 2005). Whereas this is not applicable to stored procedures.

8. Scope of a local or global temporary table in a function is limited to the parent function only. But the scope of a local or a global temporary table is accessible across the children in a stored procedure.

Example:
*  If suppose a user has created a local temporary table in a function and is immediately calling another function. Then that particular temporary table is not accessible to the called(Child) function.
*  Whereas in stored procedure, if a user creates a local temporary table and calls another stored procedure from there, then that local temporary table is accessible to the called(Child) SP.

9. You cannot call stored procedure using SELECT statement, whereas you can call a function using it.
 
I have found these differences while performing different operations. And if the reader finds some, then please share it!!!!!
 
Akshay Sahu
Microsoft Certified Technology Specialist (SQL Server 2005)