Difference between stored procedure and functions (UDF)

Functions

•     can be used with Select statement
•     Not returning output parameter but returns Table variables
•     You can join UDF
•     Can not be used to change server configuration
•     Can not be used with XML FOR clause
•     Can not have transaction within function

Stored Procedure

•     have to use EXEC or EXECUTE
•     return output parameter
•     can create table but won’t return Table Variables
•     you can not join SP
•     can be used to change server configuration
•     can be used with XML FOR Clause
•     can have transaction within SP

Functions

• can be used with Select statement


• Not returning output parameter but returns Table variables


• You can join UDF


• Can not be used to change server configuration


• Can not be used with XML FOR clause


• Can not have transaction within function

Stored Procedure

• have to use EXEC or EXECUTE


• return output parameter


• can create table but won’t return Table Variables


• you can not join SP


• can be used to change server configuration


• can be used with XML FOR Clause


• can have transaction within SP

- See more at: http://www.sql-server-citation.com/2007/02/difference-between-stored-procedure-and.html#sthash.kf7AqivX.dpuf

Functions

• can be used with Select statement


• Not returning output parameter but returns Table variables


• You can join UDF


• Can not be used to change server configuration


• Can not be used with XML FOR clause


• Can not have transaction within function

Stored Procedure

• have to use EXEC or EXECUTE


• return output parameter


• can create table but won’t return Table Variables


• you can not join SP


• can be used to change server configuration


• can be used with XML FOR Clause


• can have transaction within SP

- See more at: http://www.sql-server-citation.com/2007/02/difference-between-stored-procedure-and.html#sthash.kf7AqivX.dpuf

Functions

• can be used with Select statement


• Not returning output parameter but returns Table variables


• You can join UDF


• Can not be used to change server configuration


• Can not be used with XML FOR clause


• Can not have transaction within function

Stored Procedure

• have to use EXEC or EXECUTE


• return output parameter


• can create table but won’t return Table Variables


• you can not join SP


• can be used to change server configuration


• can be used with XML FOR Clause


• can have transaction within SP

- See more at: http://www.sql-server-citation.com/2007/02/difference-between-stored-procedure-and.html#sthash.kf7AqivX.dpuf