Stored Procedure vs User Defined Functions.

Functions :  

• Can be used with Select statement. 

•  Not returning output parameter.

• Returns table variables.  

• You can join UDF. 

• Functions are compiled and executed at run time.  

• Functions cannot affect the state of the database.  

• Function can not change server environment.

Stored Procedure: 

• Have to use EXEC or EXECUTE.

• Return output parameter.

• Won’t return Table Variables 

• You can not join SP.

• Stored procedures are stored in parsed and compiled format in the database. 

• Stored Procedures can affect the state of the databases

• Stored procedures can change server environment