Home > Articles > Data > SQL Server

  • Print
  • + Share This
From the author of

Functions That Return a Table

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')

In-line Functions

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.

  • + Share This
  • 🔖 Save To Your Account