locked
function variable scope error RRS feed

  • Question

  • I am creating a lightweight FUNCTION to get a Json element by name (the input is basically flat - except for CategoryList).

    First of, my provider is using SQL 2012, so JSON_VALUE is not available.

    I have a declared variable - @Result with global scope in the FUNCTION. At end end of the function I return the variable, but it throws the error @Result must be declared.
    Here's the function, with test data .. the logic is not complete yet, I'm not dealing with ResetFilter.

    What am I missing or doing wrong.

    Thanks

    Abbott

    if exists (select * from dbo.sysobjects where id = OBJECT_ID(N'dbo.[kbo_GetJsonElement]') )
        DROP FUNCTION dbo.kbo_GetJsonElement
    GO
    
    create FUNCTION dbo.kbo_GetJsonElement
    --===== Define I/O parameters
            (@pString VARCHAR(8000), @pKey VARCHAR(1090))
    --WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
    RETURNS VARCHAR
    BEGIN
    	DECLARE @Position INT = CHARINDEX(@pKey, @pString);
    	DECLARE @Length INT = Len(@pString);
    	SET @Length = @Length - @Position;
    	DECLARE @Result VARCHAR(8000) = SUBSTRING(@pString, @Position, @Length);
    	DECLARE @Start INT = CHARINDEX(':', @Result) + 1;
    	IF SUBSTRING(@Result, 1, 1) = '['
    		BEGIN
    			SET @Length =  CHARINDEX(']', @Result) - @Start;
    			SET @Result = SUBSTRING(@Result, @Start, @Length);
    		END
    	IF ISNUMERIC( SUBSTRING(@Result, 1, 1)) = 1
    		BEGIN
    			SET @Result - SUBSTRING(@Result, @Start + 1, @Length);
    			SET @Length =  CHARINDEX('"', @Result) - @Start);
    			SET @Result = SUBSTRING(@Result, @Start, @Length);
    		END
    	RETURN @Result;
    END
    GO
    
    DECLARE @TestJson VARCHAR(8000) = '{"FilterGuid":"bc1e991a79f94769a4747302cb224ea9","ResetFilter":false,"CategoryList":[2088,2091,2098,2066,2085,2081,2075,2084,2089,2064,2067,151,152,213,922,1562,165,1528,214,215,153,1554,167,168,1632,1600,1634,216,2008,1601,1824,1644,170,2005,171,219,172,2040,2043,173,174,1645,175,1668,2024,176,1602,1545,1543,222,223,1608,1620,2056,2007,1604,2011,177,178,1599,1569,2032,1506,227,1567,1530,180,181,229,2019,2003,1647,1542,183,1625,230,154,184,185,186,231,187,947,189,1642,1643,1629,233,234,190,1991,2049,2047,191,1583,2022,1549,1541,193,1988,194,1997,1531,1649,236,2004,2052,2023,196,237,1640,197,1584,238,198,1619,1606,1641,199,200,1624,201,239,202,1864,1603,203,1553,1598,1835,240,241,1652,205,1591,1535,1833,207,1661,208,1646,1627,209,2031,2053,210,1605,1691,1648,211,1823,1568,2035,1534,1989,924,244,245,2060,2041,2045,2018,2050,2042,2044,1626],"FromAge":6,"ToAge":12}';
    DECLARE @TestKey VARCHAR(100) = 'FromAge';
    SELECT dbo.kbo_GetJsonElement(@TestJson, @TestKey);
    GO
    
    DECLARE @TestJson VARCHAR(8000) = '{"FilterGuid":"bc1e991a79f94769a4747302cb224ea9","ResetFilter":false,"CategoryList":[2088,2091,2098,2066,2085,2081,2075,2084,2089,2064,2067,151,152,213,922,1562,165,1528,214,215,153,1554,167,168,1632,1600,1634,216,2008,1601,1824,1644,170,2005,171,219,172,2040,2043,173,174,1645,175,1668,2024,176,1602,1545,1543,222,223,1608,1620,2056,2007,1604,2011,177,178,1599,1569,2032,1506,227,1567,1530,180,181,229,2019,2003,1647,1542,183,1625,230,154,184,185,186,231,187,947,189,1642,1643,1629,233,234,190,1991,2049,2047,191,1583,2022,1549,1541,193,1988,194,1997,1531,1649,236,2004,2052,2023,196,237,1640,197,1584,238,198,1619,1606,1641,199,200,1624,201,239,202,1864,1603,203,1553,1598,1835,240,241,1652,205,1591,1535,1833,207,1661,208,1646,1627,209,2031,2053,210,1605,1691,1648,211,1823,1568,2035,1534,1989,924,244,245,2060,2041,2045,2018,2050,2042,2044,1626],"FromAge":6,"ToAge":12}';
    DECLARE @TestKey VARCHAR(100) = 'ToAge';
    SELECT dbo.kbo_GetJsonElement(@TestJson, @TestKey);
    GO
    DECLARE @TestJson VARCHAR(8000) = '{"FilterGuid":"bc1e991a79f94769a4747302cb224ea9","ResetFilter":false,"CategoryList":[2088,2091,2098,2066,2085,2081,2075,2084,2089,2064,2067,151,152,213,922,1562,165,1528,214,215,153,1554,167,168,1632,1600,1634,216,2008,1601,1824,1644,170,2005,171,219,172,2040,2043,173,174,1645,175,1668,2024,176,1602,1545,1543,222,223,1608,1620,2056,2007,1604,2011,177,178,1599,1569,2032,1506,227,1567,1530,180,181,229,2019,2003,1647,1542,183,1625,230,154,184,185,186,231,187,947,189,1642,1643,1629,233,234,190,1991,2049,2047,191,1583,2022,1549,1541,193,1988,194,1997,1531,1649,236,2004,2052,2023,196,237,1640,197,1584,238,198,1619,1606,1641,199,200,1624,201,239,202,1864,1603,203,1553,1598,1835,240,241,1652,205,1591,1535,1833,207,1661,208,1646,1627,209,2031,2053,210,1605,1691,1648,211,1823,1568,2035,1534,1989,924,244,245,2060,2041,2045,2018,2050,2042,2044,1626],"FromAge":6,"ToAge":12}';
    DECLARE @TestKey VARCHAR(100) = 'CategoryList';
    SELECT dbo.kbo_GetJsonElement(@TestJson, @TestKey);
    GO

    Monday, August 3, 2020 2:10 PM

All replies

  • I resolved it by putting a BEGIN after the sets of declares and added another END prior to the GO.

    This is interesting. It only lost scope a few lines into the Function logic???

    Abbott

    create FUNCTION dbo.kbo_GetJsonElement
    --===== Define I/O parameters
            (@pString VARCHAR(8000), @pKey VARCHAR(1090))
    --WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
    RETURNS VARCHAR
    BEGIN
    	DECLARE @Position INT = CHARINDEX(@pKey, @pString);
    	DECLARE @Length INT = Len(@pString);
    	SET @Length = @Length - @Position;
    	DECLARE @Result VARCHAR(8000) = SUBSTRING(@pString, @Position, @Length);
    	DECLARE @Start INT = CHARINDEX(':', @Result) + 1;
    	BEGIN
    		IF SUBSTRING(@Result, 1, 1) = '['
    			BEGIN
    				SET @Length =  CHARINDEX(']', @Result) - @Start;
    				SET @Result = SUBSTRING(@Result, @Start, @Length);
    			END
    		IF ISNUMERIC( SUBSTRING(@Result, 1, 1)) = 1 OR
    			SUBSTRING(@Result, 1, 1) LIKE '[a-zA-Z][a-zA-Z]%'
    			BEGIN
    				SET @Result = SUBSTRING(@Result, @Start + 1, @Length);
    				SET @Length =  CHARINDEX(',', @Result) - @Start;
    				SET @Result = SUBSTRING(@Result, @Start, @Length);
    			END
    
    		RETURN @Result;
    	END
    END
    GO

    Monday, August 3, 2020 2:57 PM
  • I think you may also want to add returns varchar(8000) and add AS right after returns keyword.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, August 3, 2020 3:19 PM
  • You don't need the extra BEGIN END pair.  Your first example had syntax errors in it.  In particular, it had

    SET @Length =  CHARINDEX('"', @Result) - @Start);

    That has an extra right parenthesis.  If you change it to

    SET @Length =  CHARINDEX('"', @Result - @Start);

    Then the code like the following works without the extra BEGIN END

    create FUNCTION dbo.kbo_GetJsonElement
    --===== Define I/O parameters
            (@pString VARCHAR(8000), @pKey VARCHAR(1090))
    --WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
    RETURNS VARCHAR(8000)
    BEGIN
    	DECLARE @Position INT = CHARINDEX(@pKey, @pString);
    	DECLARE @Length INT = Len(@pString);
    	SET @Length = @Length - @Position;
    	DECLARE @Result VARCHAR(8000) = SUBSTRING(@pString, @Position, @Length);
    	DECLARE @Start INT = CHARINDEX(':', @Result) + 1;
    	IF SUBSTRING(@Result, 1, 1) = '['
    		BEGIN
    			SET @Length =  CHARINDEX(']', @Result) - @Start;
    			SET @Result = SUBSTRING(@Result, @Start, @Length);
    		END
    	IF ISNUMERIC( SUBSTRING(@Result, 1, 1)) = 1
    		BEGIN
    			SET @Result = SUBSTRING(@Result, @Start + 1, @Length);
    			SET @Length =  CHARINDEX('"', @Result - @Start);
    			SET @Result = SUBSTRING(@Result, @Start, @Length);
    		END
    	RETURN @Result;
    END
    GO

    Note that Naomi is correct.  You should always specify the length of varchar (and char, nvarchar, etc) columns and variables.  If you don't, sometimes you get varchar(1) and sometimes varchar(50) depending on the context when you declared the column or variable.

    Tom

    • Proposed as answer by Naomi N Monday, August 3, 2020 5:28 PM
    Monday, August 3, 2020 5:03 PM
  • Hi Tom,

    You'll get varchar(30) as default if I remember correctly (don't think it changed).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, August 3, 2020 5:29 PM
  • ...

    When n isn't specified in a data definition or variable declaration statement, the default length is 1. If n isn't specified when using the CAST and CONVERT functions, the default length is 30.

    https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-ver15

    Monday, August 3, 2020 5:34 PM
  • So, in this particular case it'll be 1, right? 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, August 3, 2020 5:35 PM
  • yes.
    Monday, August 3, 2020 5:45 PM
  • Hi Tom,

    You'll get varchar(30) as default if I remember correctly (don't think it changed).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Yes.  You and Tom Phillips are correct.  It's either 1 or 30.  I had forgotten the exact values since I always specify the length.  My main point was that the length should always be specified.  If you don't specify the length not only do you need to remember when the length is 1 and when it is 30, you are depending on everyone who maintains your code in the future knows that.

    Tom

    Monday, August 3, 2020 6:46 PM
  • I wish the length was just required.  That would resolve this issue.

    There was a case I cannot remember now, where the default would be 10.  I cannot find reference now.  Not sure if that is still the case.

    Monday, August 3, 2020 6:55 PM
  • There was a case I cannot remember now, where the default would be 10.  I cannot find reference now.  Not sure if that is still the case.

    Maybe you are thiking of the str() function? It hs a default width of 10.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, August 3, 2020 9:24 PM
  • Hi Abbott,

    As mentioned by other experts, you could add the length to 'returns varchar'.

    Before(returns only 1 char):

    After(returns complete result):

    Best Wishes

    Melissa 


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 4, 2020 2:18 AM
  • Hi Abbott,

    Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

    Thank you for understanding!


    Best regards,
    Melissa


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 6, 2020 1:40 AM