DIFFERENCES BETWEEN STORED PROCEDURE AND FUNCTION IN SQL SERVER

1. You have to execute a stored procedure and you have select from the function.

2. You cannot use a stored procedure in a where clause.You can use a function in a where clause.

3. They don’t return a value by default.They only returns an integer value saying whether the sp is executed sucessfully or not.
   Functions by default return a value.The return value can be a single value or a table.

4. If you want to return any value use output parameters in stored procedures.NO concept of output parameters in functions as they by default returns a value.

5. Stored procedures are better for performance as compared to function because they retain the execution plan and reuses it for the next execution.You cannot achieve this in functions.

6. Functions are used basically used for computations but stored procedures are used for performing business logic.

7. Functions do not return ntext, image and timestamp data types as return type but stored procedure returns all types of data.

8. Only functions and extended stored procedures can be executed from within a function.You can execute a function from a stored procedure.

9. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function cannot be used for this.

10.XML and output parameters cannot be passed to functions whereas it can be with stored procedures.

11.Transactions related statements can be used in Stored procedures but functions.

12.Stored procedure can call another function and a stored proc.Function can only call another function or extended stored procedure.Function cannot call a stored procedure.

13.Functions cannot run independently. They have to be part of a SQL statement.Stored procedures can run independently.

14.You cannot create a temp table in a function but you can create a temp table in a stored procedure.

15.Functions wont allow insert , update , delete from tables. You can perform these operations in a stored procedure.

16.Stored procedures can create table variable and cannot return the table variable.But the function can create, update and delete the table variable. It can return table variable.

17.Stored procedure can have the dynamic sql statement and which can be executed using sp_execute sql statement.But the function cannot execute the sp_executesql statement.

18.Stored procedure allows getdate() or other non-deterministic functions can be allowed.The function will not allow using non-deterministic functions like GETDATE().

Correct me if any of the above point requires modifications.

Pawan Kumar
Pawankkmr@hotmail.com