CODEWITHSUNDEEP
databasestored-procedurescountsnowflake-cloud-data-platformexecute-immediate
Beaver
Beaver

Reputation: 25

How to get COUNT result from the snowflake stored procedure?

Here is my stored procedure:

CREATE OR REPLACE PROCEDURE TCT_WEBAPP.DBO.COUNT_FILTERED_PROVIDERS(
    "TIER" VARCHAR(20), 
    "CITY" VARCHAR(20), 
    "TYPE" VARCHAR(20),
    "IS_DESCENDING" BOOLEAN DEFAULT FALSE
)
RETURNS FLOAT
LANGUAGE SQL
EXECUTE AS OWNER
AS
DECLARE
    stmt VARCHAR;
    res FLOAT;
BEGIN
    stmt := 'SELECT COUNT(*) AS TOTAL_ROWS FROM ' || :PROVIDERS_TABLE_NAME || '
      WHERE 1 = 1';

    IF (LOWER(:CITY) <> 'all') THEN
        stmt := stmt || ' AND PROVIDERCITYADJUSTED = ''' || :CITY || '''';
    END IF;

    IF (LOWER(:TIER) <> 'all') THEN
        stmt := stmt || ' AND STANDARDTIER = ''' || :TIER || '''';
    END IF;

    IF (LOWER(:TYPE) <> 'all') THEN
        stmt := stmt || ' AND PROVIDERTYPEADJUSTED = ''' || :TYPE || '''';
    END IF;

    res := (EXECUTE IMMEDIATE stmt);
    RETURN res;
END;

I was using similar structure to get table and everything works fine. Then I tried to rewrite method, so now it returns me amount of rows in the filtered table. But when I'm trying to use it:

CALL COUNT_FILTERED_PROVIDERS('all', 'all', 'Clinic', 'Providers_123');

I'm getting this error:

Invalid expression value (?SqlExecuteImmediateDynamic?) for assignment.

Could some one please explain me how to EXECUTE stmt in the right way and return cound of rows? Thanks

Upvotes: 0

Views: 534

Answers (1)

NickW
NickW

Reputation: 9818

A query returns a resultset. If you wanted to return this from an SP then you’d need to treat/return it as a table.

If you want a single value you’d need to assign it to a variable and return the variable. You can do the assigning with a SELECT… INTO construct or by processing the resultset.

All this is covered in the Snowflake documentation with plenty of examples

Upvotes: 0

Related Questions