By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Ext. SPs

P: n/a
I need to be able to create my own MSSQ UDF to return the current system
date, offset by a configurable number of minutes, depending on a value in a
table. The reason I am trying to do this is, I want to be able to, for
testing purposes, fake the system into thinking that time has elapsed.
Changing the time on the computer is not an option.

I would like to call the UDF MyGetDate and to replace all code occurrence of
getdate() in the database with this call. This includes column default value
constraints and stored procedures.

The problem is that MSSQL does not allow the function GETDATE with a UDF. I
thought to try an fake it out by having the UDF call a SP, which in turn
called GETDATE. When I did this, I got the error 'Only functions and
extended stored procedures can be executed from within a function.'

I guess I can go down the road to try and learn how to write an extended
stored procedure to return the current time, but I imagine that there is a
learning curve here.

I realize that all COLUMN default CONSTRAINT with GETDATE could be handled
by create ADD AND UPDATE TRIGGERS that populate this

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
It's a kludge but you can create a UDF that reads a value from a table.
Then schedule a Sql Agent Job to run every minute, updating that
table's value.

Jul 23 '05 #2

P: n/a
Chad (ch**************@unisys.com) writes:
I need to be able to create my own MSSQ UDF to return the current system
date, offset by a configurable number of minutes, depending on a value
in a table. The reason I am trying to do this is, I want to be able to,
for testing purposes, fake the system into thinking that time has
elapsed. Changing the time on the computer is not an option.

I would like to call the UDF MyGetDate and to replace all code
occurrence of getdate() in the database with this call. This includes
column default value constraints and stored procedures.

The problem is that MSSQL does not allow the function GETDATE with a
UDF. I thought to try an fake it out by having the UDF call a SP, which
in turn called GETDATE. When I did this, I got the error 'Only functions
and extended stored procedures can be executed from within a function.'

I guess I can go down the road to try and learn how to write an extended
stored procedure to return the current time, but I imagine that there is a
learning curve here.


Using a UDF in all sorts of constraints, could have performance issues,
and if that UDF calls an extended procedure that does not make things
better.

You could save the show with:

CREATE FUNCTION kalle(@d datetime) RETURNS datetime AS
BEGIN
RETURN dateadd(DAY, 12, @d)
END
go
select dbo.kalle(getdate())

Yes, that will be somewhat bulkier, but it should get the job one.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.