How to pass a CASE statement as a Snowflake query binding

Question

I am trying to pass a CASE statement similar to the one below as a column in a SELECT statement on a Snowflake query. I am doing this by creating bindings using SqlKata in a .NET application and running the query.

Binding Key: :parameterizedColumn0

Binding Value: "CASE WHEN DEPARTMENT LIKE '%Dallas%' THEN 'Home' ELSE 'Other' END"

SELECT Statement: SELECT Column1, Column2, Column3, :parameterizedColumn0 FROM MyTable

The issue I am facing is the case statement is showing up as a string, exactly as shown above in the results, instead of actually generating the values based on the results of the case statement.

I have also tried using IDENTIFIER(:parameterizedColumn0) but end up getting syntax errors.

Does anyone know how I can actually get the results of the case statement to display in place of my binding?

Answer 1

create or replace table example(dep text, col2 number, col3 number) 
as select * from values 
    ('aaa',1,11),    
    ('bbb',2,22),  
    ('ccc',3,33);

so wanting results like:

select 
    col2, 
    col3, 
    case when dep like '%aa%' then 'home' else 'other' end as statement
from example;

so using SnowflakeScripting to emulating binded calls:

DECLARE
  rs RESULTSET;
  query TEXT DEFAULT 'select col2, col3, case when dep like ''%aa%'' then ? else ? end as statement from example';
  val1 TEXT DEFAULT 'home2';
  val2 TEXT DEFAULT 'other2';
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (val1, val2));
  RETURN TABLE(rs);
END;

works, now scripting does not allow direct value insertion so this code does not work:

DECLARE
  rs RESULTSET;
  query TEXT DEFAULT 'select col2, col3, case when dep like ''%aa%'' then ? else ? end as statement from example';
BEGIN
    rs := (EXECUTE IMMEDIATE :query USING ('home2', 'other2'));
  RETURN TABLE(rs);
END;

Anyways if we swap to the parameter being the binded thing:

DECLARE
  rs RESULTSET;
  query TEXT DEFAULT 'select col2, col3, ? as statement from example';
  val1 TEXT DEFAULT 'case when dep like ''%aa%'' then ''home3'' else ''other4'' end';
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (val1));
  RETURN TABLE(rs);
END; 

We get the problem you describe.

So longer story to say, yes, there are not way to inject whole logic blocks.

But if you know the logic you want say CASE WHEN ? like ? then ? else ? end you can pass the table, values, and filter:

DECLARE
  rs RESULTSET;
  query TEXT DEFAULT 'select col2, col3, case when IDENTIFIER(?) like ? then ? else ? end as statement from example';
  val1 TEXT DEFAULT 'home5';
  val2 TEXT DEFAULT 'other5';
  filter TEXT DEFAULT '%aa%';
  tab TEXT default 'dep';
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (tab, filter, val1, val2));
  RETURN TABLE(rs);
END;

which means you can to the string injection perhaps in the C# layer, and bind the values.