UDF Forms: Scalar, Inline Table-Valued, and Multi-statement Table-Valued Function

Scalar Functions

There are three forms of UDF defined in SQL Server T-SQL language. The first returns a scalar (single item) result. Its form is:

CREATE FUNCTION [ owner. ] fn_name ( [ { @parameter [ AS ] data_type }[ ,...n ] ] )
RETURNS data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END

 

Here's an example that raises a number to an exponent:

CREATE FUNCTION dbo.circlearea ( @radius float)
RETURNS float
AS
BEGIN
RETURN power(@radius , 2) * pi()
END

Calling this function using these Query Analyzer commands

USE PUBS
GO
DECLARE @result float
SET @result = dbo.circlearea( 3)
PRINT @result

Will yield this result:
28.2743

Inline Table-Valued Functions

Inline Table-Valued Functions return a resultset, as opposed to a single scalar value. A table valued function specifies the TABLE keyword in its RETURN clause. Its basic form is :

CREATE FUNCTION [ owner_name. ] fn_name ( [ { @parameter [ AS ] type } [ ,...n ] ])
RETURNS TABLE
[ AS ]
RETURN [ ( ] select-statement [ ) ]

An Inline Table-Valued Function created by this command:

CREATE FUNCTION datesales (@deadline as datetime)

RETURNS TABLE
AS
RETURN ( SELECT *
FROM sales

WHERE ord_date > @deadline)
 

and called by this sequence:

USE PUBS
GO
select * from datesales('09/13/1994')

will yield the following table:

stor_id	ord_num	ord_date	qty	payterms	title_id
6380	6871	09/14/94	5	Net 60	BU1032
7067	D4482	09/14/94	10	Net 60	PS2091
7131	N914008	09/14/94	20	Net 30	PS2091
7131	N914014	09/14/94	25	Net 30	MC3021
8042	423LL922	09/14/94	15	ON invoice	MC3021
8042	423LL930	09/14/94	10	ON invoice	BU1032

Multi-statement Table-Valued Function

The final type of UDF is the Multi-statement Table-Valued Function. This UDS also returns a resultset, like the Inline variety UDF, but with a much more powerful result. The Multi-statement UFD can actually create a temporary table, specifying the fields, their type and characteristics. Now the scope of that temporary table is limited to the UDF in which it was defined, so when the UDF ends, the temporary table evaporates too. However, this can be a tremendously powerful technique to obtain or manipulate data.

 

The general form for the Multi-statement Table-Valued Function is: CREATE FUNCTION [ owner_name. ] fn_name ( [ { @parameter [AS] type } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ AS ]
BEGIN
function_body
RETURN
END

The following creates the datesales2 UDF:

CREATE FUNCTION datesales2 (@deadline datetime) RETURNS @table TABLE (stor_id varchar(6) null, ord_num varchar(8) null, ord_date datetime null, qty int, payterms varchar(20), title_id varchar(6))
AS
BEGIN
INSERT @table
SELECT *
FROM sales
WHERE ord_date > @deadline
RETURN
END

 

When the function is called by this sequence,

USE PUBS
GO
select * from datesales2('09/13/1994')

The following resultset is displayed and available. (It is the same set displayed in the Inline UDF, but created with the temporary table.)

stor_id	ord_num	ord_date	qty	payterms	title_id
6380	6871	09/14/94	5	Net 60	BU1032
7067	D4482	09/14/94	10	Net 60	PS2091
7131	N914008	09/14/94	20	Net 30	PS2091
7131	N914014	09/14/94	25	Net 30	MC3021
8042	423LL922	09/14/94	15	ON invoice	MC3021
8042	423LL930	09/14/94	10	ON invoice	BU1032

Now these temporary tables by me combined, joined, with virtually any number created within the Multi-Tabled UDF, giving the user a very powerful tool for calculating and presenting data from the server in a way that truly leverages the effectiveness of writing code in the native language of SQL Server.

Metro NY / NJ SQL Server Consultants

We specialize is custom database software. Call us for a free consultation (973) 635 0080 or email us at paladn.com

 

Database programming: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, Inc. Home Page
Computer Consulting: Contact Us

		Copyright © 2001 by Paladin Consultants,Inc. Chatham, NJ All Rights Reserved This page revised   May 23, 2006