Forum Discussion
SQL Server Scalar UDF inline
I m currently using the below SQL Server 2019 with compatibility 150 Microsoft SQL Server 2019 (RTM-CU18-GDR) (KB5021124) - 15.0.4280.7 (X64) Jan 23 2023 12:37:13 Copyright (C) 2019 Microsoft Corpor...
You haven't mentioned what the UDF do and how the "different results" look like, so difficult to guess nor do we know how to reproduce.
You have to provide more details.
You have to provide more details.
- hi, here is a demo version of the function and the sample call to it with output
-- function used to get value for a key
CREATE FUNCTION [dbo].[fn_ForDemo] (@mSettings NVARCHAR(1000),
@mKey NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @mValue NVARCHAR(100)
DECLARE @mKeyValue NVARCHAR(200)
IF @mSettings IS NULL OR @mKey IS NULL
BEGIN
SET @mValue = NULL
END
ELSE
BEGIN
SELECT @mKeyValue = VALUE
FROM STRING_SPLIT(@mSettings, ';')
WHERE VALUE LIKE '%' + @mKey + '|:|%'
SELECT @mValue = Value
FROM STRING_SPLIT(@mKeyValue, ':')
IF @mValue LIKE '|%'
SET @mValue = SUBSTRING(@mValue, 2, LEN(@mValue) - 1)
IF @mValue LIKE '%|'
SET @mValue = SUBSTRING(@mValue, 1, LEN(@mValue) - 1)
END
RETURN COALESCE(@mValue, '')
END
GO
SELECT dbo.fn_ForDemo('abc|:|123|;|def|:|345|;|ghy|:|678|;|jkl|:|999|;|mno|:|888'
,'jkl')
--output= jkl(incorrect)
SELECT dbo.fn_ForDemo('abc|:|123|;|def|:|345|;|ghy|:|678|;|jkl|:|999|;|mno|:|888'
,'jkl')
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))
--output= 999 (correct)
