Using Built in Functions in User Defined Functions

If you follow the various newsgroups on Microsoft SQL Server and other user groups, you often see people asking, ‘Is there any way to use GETDATE() inside a user defined function?’. The answer to this simple question is NO. But there is way to do this. In this article I will explain how to you built_in functions inside a UDF.

As we know that SQL Server does not allow you to use a Built-in functions that can return different data on each call inside user-defined functions. The built-in functions that are not allowed in user-defined functions are:

GETDATE
GETUTCDATE
NEWID
RAND
TEXTPTR
@@CONNECTIONS
@@CPU_BUSY
@@IDLE
@@IO_BUSY
@@MAX_CONNECTIONS
@@PACK_RECEIVED
@@PACK_SENT
@@PACKET_ERRORS
@@TIMETICKS
@@TOTAL_ERRORS
@@TOTAL_READ
@@TOTAL_WRITE

If you really want to use them inside a UDF, here is the way - create a view called v_Built_in_funs and call the view inside your UDF. Here is the example:

CREATE VIEW v_Built_in_funs AS select getdate() systemdate, @@spid spid

CREATE FUNCTION fnGetNewObjects()
RETURNS TABLE
AS
RETURN ( 
SELECT name,CASE xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'L' THEN 'Log'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'Inlined table-function'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)'
WHEN 'RF' THEN 'Replication filter stored procedure '
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint (type is K)'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE NULL
END OBJECT_TYPE
FROM sysobjects, v_Built_in_Funs 
WHERE CONVERT(VARCHAR(10),crdate,101) = CONVERT(VARCHAR(10),systemdate,101))

SELECT * FROM fnGetNewObjects()