Difference Between Functions and Stored Procedures?

A stored procedure 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. (Stored procedures can also implement loops and cursors which both allow you to work with smaller results or row by row operations on data.)

The SQL Server functions are option for doing certain operations in SQL Server. 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 your 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 simplifying some logic, they can also be a performance bottleneck if they're not used properly.

User Defined Functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables. Another disadvantage is that SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions. GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create.

GETDATE () is Non-Deterministic function and it can be used in User Defined Function in SQL Server 2005.

1) Functions are used for computations. Procedures can be used for performing business logic.

2) Function parameters are always IN, no OUT is possible. Stored procedures can have input and output parameters, while user defined functions only can have input parameters. Output parameters must be returned as return values.

3) UDF can be used in the SQL Statements anywhere in the WHERE/HAVING/SELECT section Stored procedures cannot be.

4) Stored Procedure is pre compiled execution plan Functions are not precompiled.

5) Function returns only one value at a time. Stored Procedure returns more than one value at a time.

6) Functions not returning output parameter but returns Table variables.

7) We can join functions but we cannot join stored procedures.

8) Functions cannot be used to change server configuration