How to pass arguments for LIMIT and OFFSET in a Snowflake SQL stored procedure?

Question

Here is my stored procedure:

CREATE OR REPLACE PROCEDURE TCT_WEBAPP.DBO.GET_FILTERED_PAGINATION_PROVIDERS(
    "MONTH_START" VARCHAR(20), 
    "MONTH_END" VARCHAR(20), 
    "IP_FILTER" VARCHAR(100), 
    "PROPOSALS_TABLE_NAME" VARCHAR(100),
    "TIER" VARCHAR(20),
    "CITY" VARCHAR(20),
    "TYPE" VARCHAR(20),
    "PAGE_OFFSET" INTEGER,
    "PAGE_SIZE" INTEGER
)
RETURNS TABLE (
    "PROVIDERNAMEADJUSTED" VARCHAR(250), 
    "PROVIDERTYPEADJUSTED" VARCHAR(250), 
    "PROVIDERCITYADJUSTED" VARCHAR(250), 
    "STANDARDTIER" VARCHAR(250), 
    "TOTALACTUALCOST" FLOAT, 
    "TOTALCHANGE" FLOAT, 
    "TOTALCHANGEPERCENT" FLOAT
)
LANGUAGE SQL
EXECUTE AS OWNER
AS
'
DECLARE
    res RESULTSET DEFAULT (
        SELECT 
            PROVIDER.PROVIDERNAMEADJUSTED, 
            PROVIDER.PROVIDERTYPEADJUSTED, 
            PROVIDER.PROVIDERCITYADJUSTEDGROUP, 
            PROVIDER.STANDARDTIER, 
            SUM(PROPOSAL_CLAIMS.ACTUALCOST) AS TOTALACTUALCOST, 
            SUM(PROPOSAL_CLAIMS.CHANGE) AS TOTALCHANGE, 
            SUM(PROPOSAL_CLAIMS.CHANGE) / SUM(PROPOSAL_CLAIMS.ACTUALCOST) * 100 AS TOTALCHANGEPERCENT
        FROM 
            (
                SELECT 
                    c.PROGRAMNAME, 
                    c.PROGRAMTPA, 
                    c.PROVIDERCODE, 
                    c.SERVICECODE, 
                    c.NOOFUNITS, 
                    c.ACTUALCOST, 
                    c.INCURRALMONTHBRACKET, 
                    c.CLAIMTYPEIBNR, 
                    p.CLEANEDPROPOSAL, 
                    p.CLEANEDPROPOSAL * c.NOOFUNITS AS PROPOSEDCOST, 
                    PROPOSEDCOST - c.ACTUALCOST AS CHANGE 
                FROM 
                    CLAIMSDATATCT c 
                INNER JOIN 
                    TABLE(:PROPOSALS_TABLE_NAME) p ON c.SERVICECODE = p.SERVICECODE 
                WHERE 
                    c.INCURRALMONTHBRACKET >= TO_NUMBER(:MONTH_START) 
                    AND c.INCURRALMONTHBRACKET <= TO_NUMBER(:MONTH_END) 
                    AND c.CLAIMTYPEIBNR NOT LIKE 
                        CASE WHEN LOWER(:IP_FILTER) = ''exclude ip'' THEN ''Inpatient'' END
            ) AS PROPOSAL_CLAIMS
        LEFT JOIN 
            (
                SELECT 
                    programname, 
                    programtpa, 
                    providernameadjusted, 
                    providercode,
                    CASE WHEN lower(providercityadjustedgroup) = ''northern emirates'' THEN ''NE'' ELSE providercityadjustedgroup END AS providercityadjustedgroup, 
                    standardtier, 
                    CASE WHEN providertypeadjusted ILIKE ''hospital'' THEN ''Hospital'' ELSE ''Clinic'' END AS providertypeadjusted
                FROM 
                    tct_webapp.dbo.Map_Provider 
                WHERE 
                    lower(standardtier) = lower(:TIER)
                    AND lower(providercityadjustedgroup) = lower(:CITY)
                    AND lower(providertypeadjusted) = lower(:TYPE)
            ) AS PROVIDER 
        ON 
            PROPOSAL_CLAIMS.PROGRAMNAME = PROVIDER.PROGRAMNAME 
            AND PROPOSAL_CLAIMS.PROGRAMTPA = PROVIDER.PROGRAMTPA 
            AND PROPOSAL_CLAIMS.PROVIDERCODE = PROVIDER.PROVIDERCODE 
        GROUP BY 
            PROVIDER.PROVIDERNAMEADJUSTED, 
            PROVIDER.PROVIDERTYPEADJUSTED, 
            PROVIDER.PROVIDERCITYADJUSTEDGROUP, 
            PROVIDER.STANDARDTIER
         LIMIT :PAGE_SIZE
    );
BEGIN
    RETURN TABLE(res);
END;
';

I was trying to call it

CALL GET_FILTERED_PAGINATION_PROVIDERS('202201', '202212', 'Exclude IP', 'Proposals_igor', 'Tier 1', 'Dubai', 'Clinic', 15, 15);

Why am I getting this error:

Syntax error line 59 at position 25 unexpected '::'. (line 59)

I checked and it's definitely something wrong with the LIMIT statement. But I don't understand what. When I remove it, everything works fine.

If use hardcoded value like 10, it also works fine. But it doesn't work with an argument. What am I doing wrong?

Answer 1

This simplified stored procedure produces the same error:

CREATE OR REPLACE PROCEDURE TEST_LIMIT(PAGE_SIZE INTEGER)
RETURNS TABLE (N INTEGER)
LANGUAGE SQL
AS '
DECLARE
    res RESULTSET DEFAULT (
        SELECT 1
        LIMIT :PAGE_SIZE
    );
BEGIN
    RETURN TABLE(res);
END;
';

call test_limit(3);

-- syntax error line 2 at position 24 unexpected '::'. (line 2837)

A possible solution is to compose the string with the query instead:

CREATE OR REPLACE PROCEDURE TEST_LIMIT(PAGE_SIZE INTEGER)
RETURNS TABLE (N INTEGER)
LANGUAGE SQL
AS 
DECLARE
    stmt VARCHAR DEFAULT 'SELECT 1
        LIMIT ' || :PAGE_SIZE;
    res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
BEGIN
    RETURN TABLE(res);
END;
;

call test_limit(3);