Parameterize DDL SQL Snowflake

user_11105625 0
Viewed 124

In the below code is there any way I can parameterize the sst part.

I tried with concat and other methods like set ssourl=url, sst = $ssourl but of no luck. And many other methods like using concat, Identifier.

I can't parameterize cert since it has limit of 256 bytes. Is there any way I can parameterize sst in the below code. Thanks

alter account set saml_identity_provider = '{
  "cert": "XXXXXXXXXXXXXXXXXXX",
  "sst": "https://corp.com/app/<app_id>/sso/saml",
  "type"  : "test"
  "label" : "singlesignOn"
  }';
snowflake-cloud-data-platform sql variables
1 Answers

It could be done with Snowflake Scripting block:

DECLARE
  CERT STRING DEFAULT 'XXXXXXXXXXXXXXXXXXX';
  SST  STRING DEFAULT 'https://corp.com/app/<app_id>/sso/saml';
  TYPE STRING DEFAULT 'test';
  
  
  SQL STRING DEFAULT $$alter account set saml_identity_provider = '{
  "cert": "<cert>",
  "sst": "<sst>",
  "type"  : "<test>"
  "label" : "singlesignOn"
  }'$$;
BEGIN
   SQL := REPLACE(:SQL, '<cert>', :CERT);
   SQL := REPLACE(:SQL, '<sst>',  :SST);
   SQL := REPLACE(:SQL, '<type>', :TYPE);
       
   EXECUTE IMMEDIATE :SQL;
   RETURN :SQL;
END;

Output:

enter image description here

EDIT:

Using Snowflake Scripting in SnowSQL and the Classic Web Interface

EXECUTE IMMEDIATE $$

DECLARE
  CERT STRING DEFAULT 'XXXXXXXXXXXXXXXXXXX';
  SST  STRING DEFAULT 'https://corp.com/app/<app_id>/sso/saml';
  TYPE STRING DEFAULT 'test';
  
  
  SQL STRING DEFAULT 'alter account set saml_identity_provider =' || CHAR(39) || '{"cert": "<cert>",  "sst": "<sst>","type"  : "<test>"  "label" : "singlesignOn"}' || CHAR(39);
BEGIN
   SQL := REPLACE(:SQL, '<cert>', :CERT);
   SQL := REPLACE(:SQL, '<sst>',  :SST);
   SQL := REPLACE(:SQL, '<type>', :TYPE);
       
   EXECUTE IMMEDIATE :SQL;
   RETURN :SQL;
END;
$$;

Output:

enter image description here

user_50708790
Related