November 13, 2004 at 11:03 pm
hey all
i have a doubt. y is it not possible to call a non-deterministic function from another function.
but a non-deterministic function can b called from any stored procedure.
what is the logic behind this. thanx for all ur help
Rajiv.
November 14, 2004 at 1:00 pm
I guess the main reason for this is that it is by design. Actually I can't think of a valid reason for this behaviour other than Microsoft didn't implemented it this way.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 15, 2004 at 6:24 am
A user-defined functioned can invoke a nondeterministic user-defined function but it cannot invoke a built-in nondeterministic function (e.g., GETDATE).
Jeff B.
November 15, 2004 at 7:54 am
hey all
thanx for ur help. hey burtonjb plz give me an user-defined non-deterministic function example.
thanx
Rajiv.
November 15, 2004 at 8:34 am
A u.d.f. is nondeterministic if it invokes anything nondeterministic (an extended stored procedure or a nondeterministic u.d.f.).
Here is an example of a nondeterministic u.d.f. (TestNonDetUDF1) which invokes a nondeterministic u.d.f. (TestNonDetUDF2 which is taken from an example in BOL).
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.TestNonDetUDF1
(
@TableName VARCHAR(40)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @ReturnValue VARCHAR(255)
EXEC Master.dbo.xp_sprintf
@ReturnValue OUTPUT
,'INSERT INTO %s VALUES (%s, %s)',@TableName, '1','2'
RETURN @ReturnValue
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.TestNonDetUDF2
(
@TableName VARCHAR(40)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @ReturnValue VARCHAR(255)
SET @ReturnValue = dbo.TestNonDetUDF1(@TableName)
RETURN @ReturnValue
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Jeff
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply