What Are the Differences between User Defined Functions and Stored Procedures in SQL Server Transact-SQL?

Differences between user defined functions and stored procedures are:

• Stored procedures does not return any data and they can not be used in expressions.
• User defined functions does return data and they can be used in expressions.
• Stored procedures only takes data constants as input parameters.
• User defined functions can take data constants and expressions as input parameters.
• Stored procedures support output parameters.
• User defined functions do not support output parameters.
• Stored procedures take a parameter's default value, if it is not specified in the calling statement.
• User defined functions take a parameter's default value, if it is specified with the keyword DEFAULT in the calling statement.
• Stored procedures are called with no parenthesis to include parameters.
• User defined functions are called with parenthesis to include parameters. Empty parenthesis is needed if no parameters.
• Stored procedures can be created locally and temporarily.
• User defined functions can not be created locally and temporarily.

⇒Using User Defined Functions in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials