Getting 'STATEMENT_ERROR' when trying to execute `SHOW PARAMETERS` in Snowflake in block

Question

I'm trying to get JSON list of users in Snowflake account that don't have network policy set up. I've managed to compose script like this:

DECLARE res RESULTSET;
    json_result STRING;
    user_policy STRING;
    users_without_policy ARRAY; 
BEGIN
  -- Store query result in res
  res := (SELECT name FROM snowflake.account_usage.users WHERE default_role = 'SHARED_READER' AND deleted_on IS NULL);
  users_without_policy := ARRAY_CONSTRUCT();
  FOR rec IN res DO
    SHOW PARAMETERS LIKE 'NETWORK_POLICY' FOR USER rec.name;
    SELECT $2 INTO user_policy 
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) 
    LIMIT 1;
    
    -- If no network policy is set, add user to the list
    IF (user_policy IS NULL) THEN
      users_without_policy := ARRAY_APPEND(users_without_policy, rec.name);
    END IF;
  END FOR;
  
  -- Convert the list to JSON
  json_result := ARRAY_TO_JSON(users_without_policy);
  RETURN json_result;
END;

When trying to execute this code I get error:

Uncaught exception of type 'STATEMENT_ERROR' on line 15 at position 4 : SQL compilation error: Object does not exist, or operation cannot be performed.

It looks like in block I cannot call SHOW PARAMETERS. Is there any way for a workaround?

Answer 1

Based on comments and few other observations, here is a corrected version :

1.You can dynamically construct the SHOW PARAMETERS like so

cmd := 'SHOW PARAMETERS LIKE ''NETWORK_POLICY'' FOR USER "' || rec.name || '"' ;
EXECUTE IMMEDIATE cmd ;

2.I am not too sure if the value($2) column is null, I think the data is just empty, I verified it like below

SHOW PARAMETERS LIKE 'NETWORK_POLICY' FOR USER AA;
    
select  case when "value" = '' then 'test_value' end col1
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

which returned

COL1
test_value

3.Elements can be appended in array like so

  users_without_policy := ARRAY_APPEND(:users_without_policy,rec.name);

4.And finally TO_JSON to convert the array to JSON

json_result := TO_JSON(:users_without_policy);

Sample

DECLARE 
    res RESULTSET;
    json_result STRING;
    user_policy STRING;
    users_without_policy ARRAY default ARRAY_CONSTRUCT();
    cmd STRING ;
BEGIN
 
  -- Store query result in res
  res := (SELECT 'AA' name FROM dummy_users WHERE default_role = 'SHARED_READER' AND deleted_on IS NULL);
  FOR rec IN res DO
    
    cmd := 'SHOW PARAMETERS LIKE ''NETWORK_POLICY'' FOR USER "' || rec.name || '"' ;

    execute immediate cmd ;

    SELECT $2 INTO user_policy 
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) 
    LIMIT 1;
    
    -- If no network policy is set, add user to the list
    IF (user_policy IS NULL or user_policy = '') THEN
      users_without_policy := ARRAY_APPEND(:users_without_policy,rec.name);
    END IF;
  END FOR;
  
  -- Convert the list to JSON
  json_result := TO_JSON(:users_without_policy);
  RETURN json_result;
  
END;

OUTPUT