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
The below UDF returns new objects created on current day:
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))
Call the UDF to get new objects created for the day:
SELECT * FROM fnGetNewObjects()
