Microsoft SQL Server 2008 R2 Unleashed- P99:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 924 CHAPTER 29 Creating and Managing User-Defined Functions You can invoke the function by referencing it in a FROM clause as you would a table or view select from AveragePricebyType go type avg_price business mod_cook Notice that when you invoke a table-valued function you do not have to specify the schema name as you do with a user-defined scalar function. Multistatement Table-Valued Functions Multistatement table-valued functions differ from inline functions in two major ways The RETURNS clause specifies a table variable and its definition. The body of the function contains multiple statements at least one of which populates the table variable with data values. You define a table variable in the RETURNS clause by using the TABLE data type. The syntax to define the table variable is similar to the CREATE TABLE syntax. Note that the name of the table variable comes before the TABLE keyword RETURNS variable TABLE column definition table_constraint . The scope of the table variable is limited to the function in which it is defined. Although the contents of the table variable are returned as the function result the table variable itself cannot be accessed or referenced outside the function. Within the function in which a table variable is defined that table variable can be treated like a regular table. You can perform any SELECT INSERT UPDATE or DELETE statement on the rows in a table variable except for SELECT INTO. Here s an example INSERT INTO @table SELECT au_lname au_fname from authors The following example defines the inline table-valued function AveragePricebyType as a multistatement table-valued function called AveragePricebyType2 use bigpubs2008 go CREATE FUNCTION AveragePricebyType2 @price money RETURNS @table table type varchar 12 null avg_price money null AS begin insert @table SELECT type avg isnull price 0 as avg_price FROM titles group by type Download from Creating and Managing User-Defined Functions 925 .
