What is the difference between user defined functions and stored procedures?

This is one of the common interview question for experienced and fresher candidates then you people should be in a position to answer at least 4 to 5 differences. The following are the differences.

• A function will return a value where as procedure does not return a value. 

• A procedure consists of input and  output parameters where as a function will have only input parameters.

•  The select, insert, update and delete operations are possible in procedure, but in functions we can use only select operation.

• The procedure is used to create a table but not to return a table where as by using function we can return a table.

•  The execute command is used to call a stored procedure where as function will use select command only.

• The transactions are possible in stored procedures but not in functions.

•  We can call functions by using stored procedures where as stored  procedures can not be called by using functions.

•    Differed name resolution can be possible in stored procedures but not in functions.

• Joins are not used in stored procedure where as UDF will use joins.