Paladin Consultants, LLC

"Good Information is just good business"

Home
Print E-mail
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

 

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

 

 
Bookmark and Share