- New Advances in SQL Server Functions
- User-Defined Functions
- Functions That Return a Table
- Calling User-Defined Functions
- Function Determinism
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.