Differentiate between a Function and Stored Procedure?

In many instances you can accomplish the same task using either a stored procedure or a function. Both functions and stored procedures can be custom defined and part of any application. Functions, on the other hand, are designed to send their output to a query or T-SQL statement. For example, User Defined Functions (UDFs) can run an executable file from SQL SELECT or an action query, while Stored Procedures (SPROC) use EXECUTE or EXEC to run. Both are instantiated using CREATE FUNCTION.

To decide between using one of the two, keep in mind the fundamental difference between them:

• Stored procedures are designed to return its output to the application. A UDF returns table variables, while a SPROC can't return a table variable although it can create a table.
• Another significant difference between them is that UDFs can't change the server environment or your operating system environment, while a SPROC can. Operationally, when T-SQL encounters an error the function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code (provided you've included error handling support).
• You'll also find that although a SPROC can be used in an XML FOR clause, a UDF cannot be.
  

If you have an operation such as a query with a FROM clause that requires a rowset be drawn from a table or set of tables, then a function will be your appropriate choice. However, when you want to use that same rowset in your application the better choice would be a stored procedure.

There's quite a bit of debate about the performance benefits of UDFs vs. SPROCs. You might be tempted to believe that stored procedures add more overhead to your server than a UDF. Depending upon how your write your code and the type of data you're processing, this might not be the case.

A procedure does not return a value; it’s just a block of code that gets executed when called.

In C/C++ procedure and functions are same. In .NET procedures don't return value but functions do. In java there is nothing like procedures.

In database procedures are stored compiled queries and functions are in-built piece of expressions that you can use to build your queries.

Main differences between UDF and Stored Procedure

To decide between using one of the two, keep in mind the fundamental difference between them:

1. Stored procedures are designed to return its output to the application.
2. A UDF returns table variables, while a SPROC can't return a table variable although it can create a table.
3. Another significant difference between them is that UDF’s can't change the server environment or your operating system environment, while a SPROC can.
4. Operationally, when T-SQL encounters an error the function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code (provided you've included error handling support). You'll also find that although a SPROC can be used in an XML FOR clause, a UDF cannot be.

Some other differences are listed below:

1. A Function always returns a value using the return statement while a Procedure may return one or more values through parameters or may not return at all.
2. A Function can be used in the Sql Queries or SQL statement as a UDF (User-Defined Function) while a Procedure can not be used in sql queries. e.g. Functions can be called inside select statement but not the procedures.
3. We can use DDL in Procedure using Execute Immediate statement while that is not possible in Functions.
4. DML statement cannot be used in Function, but it used in Procedure.
5. Procedure can call in another project but Function work in same project.
6. We can't have any DDL, DML and TLC command inside a Function, if that function is called from a query. But if the Function is not called from query then we can have all transactional statement (DDL, DML and TLC) inside a function.
7. Functions can be part of any valid PL/SQL Expression but Procedures cannot be. We need to call procedures standalone.
8. You can use DDL statements in Functions & Procedures by using execute_immediate package in latest version and for parse sql package in old oracle version. So, regarding using of DDL or DML statements in function or procedure, there is no difference in that context.
9. Functions are basically pre-compiled, but Procedures are not. That’s why we are able to call functions from select statement but not procedure. In that case, Functions are faster than Procedures.
10. Stored Procedure accepts both Input/Output parameters where as Function accepts only input parameters. Output parameters: UDFs (User-Defined Functions) don't have the ability to return output parameters to the calling Function. They do however let us return a single scalar value or a locally created table.
11. We can use a Function inside a Procedure but vice-versa is not possible.
12. UDFs can accept a smaller number of parameters than stored procedures. UDFs can have up to 1024 parameters, where as Stored Procedures support up to 2100 parameters. This is a relatively minor limitation because most routines require a much smaller number of parameters.
13. UDFs cannot call stored procedures (except extended procedures), where as stored Procedure can call other procedures.