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

Using Built in Functions in User Defined Functions

By Nagabhushanam Ponnapalli,

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()
Total article views: 6006 | Views in the last 30 days: 6
 
Related Articles
FORUM

create function

create function

FORUM

2000 to 2005: "INITCOLVS is not a recognized built-in function name"

Error: "INITCOLVS is not a recognized built-in function name"

FORUM

exec stored proc inside a function

exec stored proc inside a function

BLOG

New built-in functions in SQL Server 2012

Introduction: Microsoft SQL Server 2012 Release Candidate 0 introduces 14 new built-in functions. T...

FORUM

constraints on columns while creating the table

constraints on columns while creating the table

Tags
miscellaneous    
t-sql    
 
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