procedure vs functions in sql server... 7 differences

Here are 7 difference i found between sql server procedure and user defined functions.

Procedure: Procedures are mainly used to process the tasks more aligned towards data stored in Database.

1. Doesn't need to return values but can return value, return statement does not have any effect .

2. Stored are compiled inside database and has execution plan ready.

3. Can affect the state of database using DML and TCL operations.

4. Cannot be invoked from SQL statements e.g. SELECT.

5. Select statement returns data to client.

6. It is allowed to create database objects. temporary and permanent objects.


Function : Functions are preferrably used to compute/calculate values.

1. Must return at least one output parameter.Can return more than one parameter using OUT argument.

Error message we get "The last statement included within a function must be a return statement.".

2. Parsed and compiled at run time.

3. Cannot affect the state of database, Means it can not have UPDATE,DELETE kind of statements

4. Can be invoked from SQL statement e.g. SELECT.

5. A simple select statement does not return data to client.
message we get is "Select statements included within a function cannot return data to a client."

6. Object can't be create inside UDF. Like table, views etc