SQL Server T-SQL User-Defined Function of the Week

Using Non-Deterministic Functions Inside UDFs

Volume 1 Number 2   December 4, 2002 (Bonus Issue)

Visit http://www.NovickSoftware.com for additional material
about SQL Server, Visual Basic, ASP, XML and security.

Visit the T-SQL UDF of the Week Archives at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeekArchive.htm

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Many programmers wonder why you can't use the getdate()
built-in function in a UDF.  The reason comes from the 
use of scalar UDFs in indexes.  To allow UDFs to be used in
indexes, the UDF must return the same result for any given
set of parameters.  If a scalar UDF uses the getdate()
function, it can return a result that is based on that date
and the result would change every time the function is
called.

When a function can return different results for the same
set of parameters it's called non-deterministic.  SQL Server
2000 attempts to prohibit the use of any technique that
would create non-deterministic scalar UDFs. And so it
prohibits the use of getdate().

It's possible to get around the limitation with the aid of a
view.  This week's UDF is udf_DT_CurrTime.  It returns the
current time as a CHAR(8) in the form HH:MM:SS.

The script to create the function must first create the VIEW.
Both are very short as you can see.


/------------- Copy From Below this line ------------------\
CREATE VIEW Function_Assist_GETDATE

/********************************************************
*
* A view to return one row, with one column, the current 
* date/time from the built-in function GETDATE().  This 
* view allows a UDF to bypass the restriction on access to 
* the non-deterministic getdate() function.
*
* Attribution: Based on a newsgroup posting in by Mikhail 
*   Berlyant in microsoft.public.sqlserver.programming 
* 
* Common Usage:
DECLARE @dtVar datetime
select @dtVAr = [GetDate] from Function_Assist_GETDATE
**********************************************************/

AS 
    SELECT getdate() as [GetDate]

GO

GRANT SELECT on Function_Assist_GETDATE to PUBLIC
GO

-- Next we create the function.
GO 

CREATE FUNCTION udf_DT_CurrTime ()

    RETURNS  CHAR(8) -- Current Time string in the form 
                     -- HH:MM:SS using 24 hour clock

/***************************************************************
*
* Returns the time as a CHAR(8) in the form HH:MM:SS 
* This function bypasses SQL Server's usual restriction 
* against using getdate by selecting it from a view.
*
* Related Objects: Function_Assist_Getdate
* Attribution: Based on news group posting by Mikhail
*    Berlyant in microsoft.public.sqlserver.programming
*
* Common Usage:
select dbo.udf_DT_CurrTime()
*
* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
***************************************************************/

AS BEGIN 

    DECLARE @CurrDate datetime

    Select @CurrDate = [GetDate] 
        From Function_Assist_Getdate

    return Convert (char(8), @currDate, 108) 
        -- 108 is HH:MM:SS 24 hour clock

END
GO

GRANT EXECUTE on udf_DT_CurrTime to PUBLIC
GO

\------------ stop copying above this line ---------------/

I use udf_DT_CurrTime at the beginning of each SQL PRINT
statement in a batch to give it a time stamp.  An example of
one PRINT statement is in the following script:

/------------- Copy From Below this line ------------------\
Declare @Rc Int
Set @Rc = 17 -- Or use it in an exec @Rc = Sp_......
Print Dbo.Udf_Dt_Currtime()
      + ' Return Code = ' 
      + convert(varchar, @rc)
go
\------------ stop copying above this line ---------------/

By the way.  Now that we've created a non-deterministic
scalar UDF.  Don't try and use it in an index. That would
be much worse than running with scissors.

+---------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe
you have a T-SQL problem that you think could be solved by
a UDF but you don't know how? Please send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try 
and respond to every request that I get, even if I 
can't help.  

Thanks,  
Andrew Novick
+---------------------------------------------------------+

This newsletter is published by Novick Software 
http://NovickSoftware.com
Copyright (c) 2002 Novick Software. All rights reserved.