I have the following function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[IP4toBIGINT](
@ip4 varchar(15)
)
RETURNS bigint
WITH SCHEMABINDING
AS
BEGIN
-- oc3 oc2 oc1 oc0
-- 255.255.255.255
-- Declared as BIGINTs to avoid overflows when multiplying later on DECLARE @oct0 bigint, @oct1 bigint, @oct2 bigint, @oct3 bigint;
DECLARE @Result bigint;
SET @oct3 = CAST(PARSENAME(@ip4, 4) as tinyint);
SET @oct2 = CAST(PARSENAME(@ip4, 3) as tinyint);
SET @oct1 = CAST(PARSENAME(@ip4, 2) as tinyint);
SET @oct0 = CAST(PARSENAME(@ip4, 1) as tinyint);
-- Combine all values, multiply by 2^8, 2^16, 2^24 to bitshift.
SET @Result = @oct3 * 16777216 + @oct2 * 65536 + @oct1 * 256 + @oct0;
RETURN @Result;
END
But...
SELECT
OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'IsDeterministic') as IsDeterministic
,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'IsPrecise') as IsPrecise
,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'IsSystemVerified') as IsSystemVerified
,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'SystemDataAccess') as SystemDataAccess
,OBJECTPROPERTYEX(OBJECT_ID('dbo.IP4toBIGINT'), 'UserDataAccess') as UserDataAccess
Returns (result transposed):
IsDeterministic 0
IsPrecise 1
IsSystemVerified 1
SystemDataAccess 0
UserDataAccess 0
I tried dropping and recreating the function several times to make sure it's not some caching issue. CAST should be deterministic here since I'm using it for strings->integers.
I'm completely stumped, any ideas?