Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Differences between Functions and Stored Procedures

By: FYIcenter.com

(Continued from previous topic...)

What Are the Differences between User Defined Functions and Stored Procedures?

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.

(Continued on next topic...)

1. What Are User Defined Functions?
2. What Are the Differences between User Defined Functions and Stored Procedures?
3. How To Create a Simple User Defined Function?
4. How To Use User Defined Functions in Expressions?
5. How To List All User Defined Functions in the Current Database?
6. How To Drop an Existing User Defined Function?
7. How To Generate CREATE FUNCTION Script on an Existing Function?
8. How To Get the Definition of a User Defined Function Back?
9. How To Modify an Existing User Defined Function?
10. How To Create User Defined Functions with Parameters?
11. How To Provide Values to User Defined Function Parameters?
12. Can You Pass Expressions to Function Parameters?
13. How To Provide Default Values to Function Parameters?
14. How Many Categories of Functions based Their Return Modes?
15. How Many Ways to Create Table-Valued Functions?
16. How To Create an Inline Table-Valued Function?
17. How To Create an Multi-Statement Table-Valued Function?