Function vs. Stored Procedure in SQL Server
Functions are computed values and cannot perform permanent environmental changes to SQL Server
(i.e., no INSERT
or UPDATE
statements allowed).
A function can be used inline in SQL
statements if it returns a scalar value or can be joined upon if it returns a result set.
A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:
Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters
(the arguments). Functions are not allowed to change anything, must
have at least one parameter, and they must return a value. Stored
procs do not have to have a parameter, can change database objects,
and do not have to return a value.
Functions vs Stored Procedures
If you're likely to want to combine the result of this piece of code with other tables, then obviously a table-valued function will allow you to compose the results in a single SELECT statement.
Generally, there's a hierarchy (View < TV Function < Stored Proc). You can do more in each one, but the ability to compose the outputs, and for the optimizer to get really involved decreases as the functionality increases.
So use whichever one minimally allows you to express your desired result.
Related Topics
Which Is Faster/Best? Select * or Select Column1, Colum2, Column3, etc
Calculate a Running Total in MySQL
SQL Join - Where Clause Vs. on Clause
SQL Query Return Data from Multiple Tables
How Stuff and 'For Xml Path' Work in SQL Server
Insert into ... Values ( Select ... from ... )
Sql, Auxiliary Table of Numbers
When Do I Need to Use a Semicolon VS a Slash in Oracle Sql
Difference Between Lateral Join and a Subquery in Postgresql
MySQL Query Finding Values in a Comma Separated String
MySQL How to Fill Missing Dates in Range
Best Way to Do Multi-Row Insert in Oracle
Identity Increment Is Jumping in SQL Server Database
Pass R Variable to Rodbc'S Sqlquery