non-deterministic functions in udf

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

non-deterministic functions in udf

Author

Topic

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-14 : 08:35:12

you can't use
select getdate() in a udf
but you can create a view which returns getdate() and select that to have the same effect - probably the same for the others

In the same way you can't call an SP from a udf.
But you can define a linked server to your own server and use openquery to call the SP in the function.

create function myf
()
returns table
as
return select * from openquery(SELF,'exec sp_who')
go

Anyone know why these aren't allowed in udf's and whether there is a problem incorporating them in this way?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-14 : 09:21:13

From BOL

quote:

Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are:

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

The types of statements that are valid in a function include:

DECLARE statements can be used to define data variables and cursors that are local to the function.

Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.

Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.

Control-of-flow statements.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.

EXECUTE statements calling an extended stored procedure.

Edited by - ValterBorges on 01/14/2003 09:23:04

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-14 : 09:41:05

how do you define a linked server to yourself? it says it isn't allowed :(

CREATE FUNCTION dbo.Today() RETURNS DATETIME AS
BEGIN
RETURN (SELECT CAST(CONVERT(VARCHAR(10), Today, 101) + ' 00:00:00' AS DATETIME) FROM OPENROWSET('SQLOLEDB', 'Server=DBSERVER;UID=anonymous;', 'SELECT GetDate() AS Today') AS Query)
END
GO

SELECT dbo.Today()
GO

so I used that ... anonymous has no access except to certain application roles, but it can still do that at least ... thats a handy trick I wouldn't have thought of without that ... i have wanted to call stored procedures so many times from a udf ... this is most valuable... thanks nr

Edited by - onamuji on 01/14/2003 09:41:28

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-01-14 : 10:04:49

This sort of problem?


CREATE VIEW Rand_view AS SELECT RAND() AS r

GO

CREATE FUNCTION RandInt(@lo AS int, @hi AS int)
RETURNS int
AS
BEGIN
  RETURN FLOOR((SELECT r FROM Rand_view)*(@hi - @lo + 1))+@lo
END

GO

SELECT r, COUNT(*)
FROM (
    SELECT n, dbo.RandInt(1,100) AS r
    FROM Numbers
    WHERE n < 1000
  ) A
GROUP BY r
ORDER BY r

This is essentially the same problem as exhibited in the second half of this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18896

Come to think of it, the first problem in that thread will also happen. With the above definitions:


SELECT r, r, r, r, r, r, r, r, r, r
FROM (SELECT dbo.RandInt(1, 100) AS r) AS A

Edited by - Arnold Fribble on 01/14/2003 10:25:26

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-14 : 10:25:43

ValterBorges, Arnold Fribble:
The point is that non-deterministic functions/Sp calls are not allowed from UDFs but if you access them from a view or openquery they seem to work happily.

Anyone know why these aren't allowed in udf's and whether there is a problem incorporating them in this way?

It's similar to creating a temp table in a trigger which also isn't allowed via create table but is via select ... into.

Onamuji:
You can define a linked server using oledb provider for sql server, give it another name but reference your own server. This will give you a linked server reference your local server and looks like a remote linked server.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-01-14 : 10:30:17

So you tried running my example, then?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-14 : 10:48:00

Isn't it just wrapping a non-deterministic function in a view same as with getdate().

I assume it works in the same manner but is it advisable.
If it is ok then why are these functions/SPs not allowed in udfs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-01-14 : 11:30:56

quote:

Isn't it just wrapping a non-deterministic function in a view same as with getdate().

Wasn't when I tried it:


CREATE VIEW Now_view AS SELECT GETDATE() AS t

GO

CREATE FUNCTION Now()
RETURNS datetime
AS
BEGIN
  RETURN (SELECT t FROM Now_view)
END

GO

SELECT n, GETDATE() AS t1, dbo.Now() AS t2
FROM Numbers

Returned same value in t1 for all rows, different values in t2.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-14 : 12:45:11

Isn't that what you'd expect?
The server knows all about getdate() and treats it as a constant executed once - that's why you get the same getdate() for all inserted records (I hope).
A udf gets executed for each row. If the server realised it was just using getdate() it would probably refuse it rather than return the constant.

Interestingly
select distinct d = getdate(), e = dbo.now() from bigtable
order by d,e

gives duplicate rows (but it does get rid of a lot of duplicates)

select distinct d = convert(varchar(24),getdate(), 109), e = convert(varchar(24),dbo.Now(), 109) from bigtable
order by d,e

also gives duplicates but not as many.

as does
select distinct dbo.now() from bigtable
and
select dbo.now() from bigtable union select getdate

this all on v2k sp1
Be nice if someone could try it with sp2

I'm doing it with a table of about 80,000 rows and getting about 12,000 rows back of which maybe a quarter are duplicated.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 01/14/2003 12:53:23

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-14 : 18:25:49

Using 2ksp2

Table Numbers has 10,000 int from 1 - 10,000

select distinct d = getdate(), e = dbo.now() from Numbers
order by d,e

33 Results

select distinct d = convert(varchar(24),getdate(), 109), e = convert(varchar(24),dbo.Now(), 109) from Numbers
order by d,e

49 Results

select distinct dbo.now() from Numbers

29 Results

select dbo.now() from Numbers union select getdate()
34 Results

approx. 1/3 are duplicates.

Edited by - ValterBorges on 01/14/2003 18:34:46

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-15 : 06:41:33

Thanks
Looks like it might be the getdate() that is messing things up
This works ok.

create function xxx
(@id int)
returns int
as
begin
return @id
end
go

select x.a
from
(select distinct a = dbo.xxx(t.id)
from (select s.id from syscolumns s, syscolumns t) as t
) as x
group by x.a
having count(*) > 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources