Parameterize DDL SQL Snowflake-CodePudding

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"
  }';

CodePudding user response：

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:

EDIT: