Paladin Consultants, LLC

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

and called by this sequence:

will yield the following table:

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

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

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