Difference between Stored Procedure and Function in SQL Server

Stored Procedures

Ø  SP can returns zero, single or multiple vales

Ø  We can use transaction in SP

Ø  SP can have input and output parameter

Ø  We can call Sp and function on the  Stored Procedure

Ø  We can use DML,DDL and TCL command in SP

Ø  We can use Try- catch in SP

Ø  We can create temp table in SP.

Ø  We can’t use SP on  Select  statement (like Select Exec <SP>)

Ø  SP can have up to 21,000 input parameter

Function

Ø  Function must be return only one value

Ø  We can’t use transaction

Ø  We can use only input parameter

Ø  We can’t use Sp in the Function

Ø  We can use function with Select (select GetDate())

Ø  We can’t use Try Catch block

Ø  We can’t use DDL, DML and TCL command except Select

Ø  Function can’t return Image, text, ntext and Timestamp.

Ø  Function can have up to 1,023 input

Ø  Temp table we can’t create on Function