There are two types of tabular functions. If the RETURNS statement contains the TABLE data type with all the columns that are contained in the table defined, the function is known as a multistatement tabular function. If the RETURNS section of the function contains the TABLE data type with no columns listed, the function is known as an in-line function. In-line functions are table-valued functions that contain a SELECT statement as the body of the function. The returned columns and data types are derived from the SELECT statement.
Multistatement Tabular Functions
Multistatement tabular functions are a powerful alternative to views. Like a view, this type of function can be used to in a T-SQL statement in the same place you would use a table or view. Multistatement tabular functions can be joined like any other table. The code in Listing 4 outlines the creation of a multistatement tabular function.
Listing 4 Multistatement Tabular Function
CREATE FUNCTION fncOrdersByOrderNumber(@vchOrderNumber VARCHAR(12)) RETURNS @tabOrdersByOrderNumber TABLE ( StoreName VARCHAR(32), OrderNumber VARCHAR(12), Quantity INT, Title VARCHAR(128) ) AS BEGIN INSERT INTO @tabOrdersByOrderNumber SELECT st.stor_name, sa.ord_num, sa.qty, ti.title FROM sales sa JOIN stores st ON (sa.stor_id = st.stor_id) JOIN titles ti ON (sa.title_id = ti.title_id) WHERE sa.ord_num = @vchOrderNumber RETURN END GO
To test this function, all you need to do is run a SELECT statement against the function with the order number that you want to get data about. The following SELECT statement does just that for order number P2121:
SELECT * FROM fncOrdersByOrderNumber ('P2121')
The functionality provided by in-line functions enables you to achieve parameterized views. In a standard view, you specify either that all the data is to be contained in the view and then filtered during the SELECT, or that the view is to contain only a specified amount of data. Take, for example, the two views in Listing 5. The first view returns the information about all the authors in the authors table. The second view returns information about all the authors in the authors table who live in California.
Listing 5 In-line Functions
CREATE VIEW vwAllAuthors AS SELECT au_lname + ', ' + au_fname AS 'Name', phone, address, city, state, zip FROM authors GO CREATE VIEW vwAuthorsInCA AS SELECT au_lname + ', ' + au_fname AS 'Name', phone, address, city, state, zip FROM authors WHERE state = 'CA'
Some RDBMSs allow you to create views that let you pass in a parameter that filters the returned data. SQL Server doesn't, but in-line functions can provide similar functionality. The function in Listing 6 shows the creation of an in-line procedure that acts as a parameterized view.
Listing 6 Returning a Table
CREATE FUNCTION fncGetAuthorsByState(@vchState AS CHAR(2)) RETURNS TABLE AS RETURN ( SELECT au_lname + ', ' + au_fname AS 'Name', phone, address, city, state, zip FROM authors WHERE state = @vchState )
The major difference between the code in Listings 5 and 6 is that the RETURNS section doesn't list the columns that will be returned. To test this code, the following SELECT statement runs against the function while specifying the state that the user is looking for:
SELECT * FROM fncGetAuthorsByState('CA')
Now that you've seen how to create the different type of functions, you need to be aware of the different ways to call the functions that you've created.