CODEWITHSUNDEEP
sql-server
Ghulam Haider
Ghulam Haider

Reputation: 29

How can I make my SQL scalar function deterministic as well as having a variable default value in it

--DROP FUNCTION fn_NonDeterministic;
--DROP FUNCTION fn_Deterministic

CREATE FUNCTION [dbo].[fn_NonDeterministic]()
RETURNS DECIMAL (13,3)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @date AS DATETIME = '01/01/1900';
    RETURN 10.2
END
GO


SELECT ROUTINE_NAME,
       IS_DETERMINISTIC
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_NAME = 'fn_NonDeterministic'
GO

CREATE FUNCTION [dbo].[fn_Deterministic]()
RETURNS DECIMAL (13,3)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @date AS DATETIME ;
    RETURN 10.2
END
GO


SELECT ROUTINE_NAME,
       IS_DETERMINISTIC
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_NAME = 'fn_Deterministic'

Upvotes: 1

Views: 54

Answers (1)

Cato
Cato

Reputation: 3701

I don't think it likes the conversion from string to date, which is deterministic since it is affected by regional setting .

/*
dROP FUNCTION fn_NonDeterministic;
DROP FUNCTION fn_Deterministic
*/


CREATE FUNCTION [dbo].[fn_NonDeterministic]()
RETURNS DECIMAL (13,3)
WITH SCHEMABINDING
AS
BEGIN
    -- ********************************  this changes things ***********
    DECLARE @date AS datetime = datefromparts(1970,05,23);
    RETURN 10.2
END
GO


SELECT ROUTINE_NAME,
       IS_DETERMINISTIC
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_NAME = 'fn_NonDeterministic'
GO
CREATE FUNCTION [dbo].[fn_Deterministic]()
RETURNS DECIMAL (13,3)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @date AS DATETIME ;
    RETURN 10.2
END
GO


SELECT ROUTINE_NAME,
       IS_DETERMINISTIC
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_NAME = 'fn_Deterministic'

Upvotes: 1

Related Questions