Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL 2000 User Defined Function Primer

By Neil Boyle, (first published: 2001/05/02)

Introduction

Many programming languages have supported User Defined Functions for years, but they are new to SQL Server 2000. In this article we will look at some of the ways functions can be used within SQL Server 2000.

This document was developed against SQL Server 2000 Beta 2, so some points may be subject to change in the release version.

What are functions?

Functions are pre-prepared pieces of code that may accept parameters, and always return a value. Older versions of SQL Server supply some built-in functions, such as getdate() and object_name() but to be able to define your own functions is a new feature in SQL Server 2000. 

Throughout the text I will use the abbreviation UDF for User Defined Function.

All functions (not just UDFs) are split into two groups, deterministic and non-deterministic. Deterministic functions will always return the save value if provided with the same parameters. Non-deterministic functions may produce different results each time they are called, even if any supplied parameters are unchanged.

SQL Server Books Online also uses the term "scalar functions" - these are simply functions that return one value, as opposed to functions that return a table.

getdate() is a good example of both a scalar and a non-deterministic function - call it twice in a row and you will get two different date-time results, but only one value from each call.

A simple UDF

It's not uncommon to want to know the number of days in particular month, so let's look at a simple function to do this. Here is the code:

create function DaysInMonth (@when datetime) 
returns int 

as 
BEGIN 
declare @rv int

if datepart(month, @when) = 2 
begin 
    if datepart(year, @when) % 400 = 0 
    select @rv = 29 
else 
    if datepart(year, @when) % 100 = 0 
    select @rv = 28 
else 
    if datepart(year, @when) % 4 = 0 
    select @rv = 29 
else 
    select @rv = 28 
end -- if
else
begin 
select @rv = case (datepart(month, @when)) 
    when 4 then 30 
    when 6 then 30 
    when 9 then 30 
    when 11 then 30 
    else 31 
    end -- case 
end -- else

return @rv

END -- fn def 

To execute the function, 

select dbo.DaysInMonth('1 may 2000') 
select dbo.DaysInMonth(getdate())

As you can see, most of the code could have come from any SQL Server stored procedure. The new features are:

    create function DaysInMonth (@when datetime) 
    returns int 

Clearly this is a function definition header - the returns int states that the function returns a single integer value.

    return @rv

The return @rv command must be the final line of the function, and it's this instruction that sends the output value back to the call. Almost any SQL Server data type can be returned by a UDF.

Using function calls

You can code a function call anywhere you could code a variable or literal of the same data type as the function returns. For example, to calculate the number of days between now and the end of the month:

    select dbo.DaysInMonth(getdate()) - datepart(day, getdate())

Note the repeated use of the owner qualification before the UDF name, (dbo.DaysInMonth() instead of DaysInMonth() - This qualification is a SQL Server requirement in Beta 2, and will probably go forward into the release version.

Functions within functions

You can nest a function within another of you so wish.

create function daysTillMonthEnd (@when Datetime)
returns int
as
begin
return dbo.DaysInMonth(@when) - datepart(day, @when)
end

Restrictions on using functions within functions

 There is a restriction on this which I ran straight into when I first started to play with UDFs. You cannot use a built-in non-deterministic function within a user defined function. When I made my first stab at coding the DaysInMonth function shown above, I tried to use the getdate() function as a default in case no parameters were supplied in the function call. SQL Server will not allow this.  

Using other data types

You can return almost any data type in a function, including User Defined Data Types. The following simple example returns a UDDT based on the varchar data type.

sp_addtype varstring, 'varchar(32)'
go
create function capitalise (@string varstring) 
returns varstring
as
BEGIN
    return UPPER(left(@string, 1)) + 
           LOWER(substring (@string, 2, datalength(@string)))
END
go
select dbo.capitalise ('abcdef')

SQL Server 2000 will not allow you to return text, image, cursor or timestamp data types from a function.

Recursive functions

Functions have the ability to call themselves, or "recurse". The example below works out the factorial of the integer input. 

create function factorial (@i bigint) 
returns bigint
as
BEGIN

    declare @internalI bigint

    if @i > 20 OR @i IS NULL 
        select @internalI = NULL
    else 
    if @i < 2 
        select @internalI = @i
    else
        select @internalI = @i * dbo.factorial(@i - 1)

    return @internalI

END -- fn def
go
select dbo.factorial (3)

select dbo.factorial (8)

For the non-mathematically inclined, factorials are what you get when you multiply together all the whole numbers between 1 and the number you thought of. For example:

  • The Factorial of 3 = 1 x 2 x 3 = 6
  • The Factorial of 8 = 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 = 40320
  • Functions can recurse up to level 32 deep, after which SQL Server will generate an error. The example above is restricted to 20 times because Factorial 21 is too big for the bigint data type.

    Passing tables out of functions

    Create a function that passes back a table and what you end up with is a kind of "parameterized view" - something you can treat as a view, but which will return different values depending on the parameters passed in. The following example for the PUBS database takes an author_id as it's parameter and returns a table containing all the author IDs that have collaborated with them, and the title_id they worked together on.

    create function collaborator (@au_id char(11))
    returns @collaborator table (
        target_author char(11),
        title_id char(6),
        partner_author char(11)
    )
    as
    BEGIN
        insert     @collaborator
        select     l.au_id, l.title_id, r.au_id
        from       titleauthor l inner join titleauthor r
        on         l.title_id = r.title_id AND l.au_id <> r.au_id
        where      l.au_id = COALESCE(@au_id, l.au_id)
        order by   l.title_id, l.au_id

    RETURN

    END -- fn def

    select * from dbo.collaborator('472-27-2349')
    select * from dbo.collaborator(NULL)

    Note the use of the COALESCE function. We use this to return data for all authors that have collaborated on a book if no specific author_id is given.

    You could code the line create function collaborator (@au_id char(11) = NULL) to define a default value for the parameter, and SQL Server would accept this. However, if you then try calling the function without parameters (a perfectly valid thing to do with stored procedure parameters) then SQL Server will complain. Instead you have to code dbo.collaborator(DEFAULT) for the function call.

    In-line table functions

    SQL Server differentiates between "user defined functions that return a table" and "in-line table functions". The previous example is a "user defined functions that return a table". We can re-code this as an in-line function as follows:

    create function collaborator_inline (@au_id char(11))
    returns table as
    RETURN (
        select  l.au_id as "author", 
                l.title_id as "title", 
                r.au_id as "co-author"
        from    titleauthor l inner join titleauthor r
        on      l.title_id = r.title_id AND l.au_id <> r.au_id
        where   l.au_id = COALESCE(@au_id, l.au_id)
    --  order by l.title_id, l.au_id
    )
    go


    select * from dbo.collaborator_inline('724-80-9391')
    select * from dbo.collaborator_inline(NULL)

    This syntax is more compact than the previous example, but also more restrictive. While the "user defined functions that return a table" can include any amount of code, the in-line version is restricted to a single select statement. Even the "order by" clause needs to be removed from the in-line version.

    Table function in joins

    Parameterized functions that return tables may be part of a standard SQL join statement.

    select * 
    from dbo.collaborator('472-27-2349') inner join authors as a 
    on partner_author = a.au_id

    Functions as columns in views

    UDFs can be used as columns in a view. 

    Building on the daysInMonth() function we defined earlier, and a new function startOfMonth() function, we can create this NORTHWIND database view showing daily average sales figures per month without going to excessive lengths to cater for months with different numbers of days.

    create function startOfMonth (@when datetime)
    returns smalldatetime as 
    BEGIN
        return dateadd (day, 1 + datepart(day, @when) * -1, @when)
    END -- fn def
    go

    create view monthlyAverages as
    select month, 
        sum(quantity) / 
        dbo.daysInMonth(month) 
        as dailyAvgSale
        from (select dbo.startofMonth (orderDate) 
            as month,
            quantity,
            o.OrderID
            from orders o 
            inner join "order details" od
            on o.OrderID = od.OrderID
        ) as derived
    group by month
    go

    select * from monthlyAverages order by month

    Functions and constraints

    You can use functions as to define constraints, provided that both the table and the function share the same owner. This example defines a function called " midnight" that truncates a date-time to midnight, then uses this in a table's check constraint to ensure that the prevShutdown column in test_table is always at least a day earlier then the value in the startup column.

    create function midnight (@when smalldatetime)
    returns smalldatetime
    as
    BEGIN
        return convert(varchar(11), @when)
    END -- fn def
    go

    create table test_table (
        id               int identity,
        prevShutdown     smalldatetime,
        startup          smalldatetime,
        CONSTRAINT       chk_midnight CHECK (prevShutdown < dbo.midnight(startup))
    )
    go

    You can also use a function as the basis for a default constraint, but presently you are limited to using constants in the function parameters, which restricts their usefulness.

    Schema bound functions

    Particularly with older versions of SQL Server, you could get into trouble if you defined one object  that was dependent on another, then dropped or changed the subordinate object. (In plain English: if you created a view on a table, then dropped the table, the view would still exist but it would obviously not work)

    Schema binding tries to put a stop to this. If you create a function and specify it is Schema Bound, then the objects it depends on cannot be altered without you first dropping the schema binding. There are limitations as to when this will work (it will not work across multiple databases for example) but it is still a very useful feature.

    Schema binding is invoked at function creation time. We can change our collaborator() function to a schema bound version as shown below. Note that in addition to the with schemabinding option, we need to specify a two part name (dbo.titleauthor instead of just titleauthor) for all referenced objects.

    create function collaborator_sb (@au_id char(11))
    returns table 
    with schemabinding
    as
    RETURN (
        select  l.au_id as "author", 
                l.title_id as "title", 
                r.au_id as "co-author"
        from    dbo.titleauthor l inner join dbo.titleauthor r
        on      l.title_id = r.title_id AND l.au_id <> r.au_id
        where   l.au_id = COALESCE(@au_id, l.au_id)
    -- order by l.title_id, l.au_id
    )

    As long as schema binding is in place for this function, any attempt to change the structure of the titleauthor table will generate an error.

    Overloading functions

    Overloading is the practice of having multiple functions with the name name, each doing different things. The '+' operator is overloaded as it can both add up numbers and concatenate strings.

    select 1 + 2 + 3
    select '1' + '2' + '3'

    In Beta 2 at least, overloading functions is a non-starter due to the UNIQUE constraint on the sysobjects columns name and uid. There is some discussion in the Beta support newsgroups about implementing this in future, but I would not hold my breath.

    About the author

    Neil Boyle is an independent SQL Server consultant working out of London, England. Neil's free SQL Server guide is available on-line at http://www.impetus-sql.co.uk

    Total article views: 20651 | Views in the last 30 days: 24
     
    Related Articles
    FORUM

    create function

    create function

    FORUM

    Help Needed - Function Returning Table

    Function Returning Table

    FORUM

    I need to create a function that returns a date 6 months from getutc date

    I need to create a function that returns a date 6 months from getutc date

    FORUM

    Return result of dynamic query from function

    Return result of dynamic query from function

    Tags
     
    Contribute

    Join the most active online SQL Server Community

    SQL knowledge, delivered daily, free:

    Email address:  

    You make SSC a better place

    As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

    Join us!

    Steve Jones
    Editor, SQLServerCentral.com

    Already a member? Jump in:

    Email address:   Password:   Remember me: Forgotten your password?
    Steve Jones