Dynamic Query: Implementation "exec sp_executesql"

Follow

0

Leo Ajdinovic

• December 11, 2020 23:51

Hello,

I want to build a dynamic query like this one:

 

SELECT CAST(STRING_AGG(Co1,', ') AS STRING) AS Co1
into #Temp1
FROM Table1;

declare string ColName = (select Co1 from #Temp1);

 

Afterwards I want to be able to select ColNames defined above. In SQL, one can use "exec sp_executesql". However, in data virtuality seems not to work. Any ideas?

I tried couple of things, including following this one: https://datavirtuality.com/docs/content/2.1/Reference%20Guide/Dynamic_SQL_Command.html

Thank you in advance.

 

• Comment actions Permalink

  Official comment

  

  Nick Golovin

  • December 01, 2021 13:30

  Let me add an example solution here from Zendesk for public reference:

   

  begin
      create local temporary table #Temp1 (col1 string);
      declare string ex_string= 'SELECT CAST(STRING_AGG(col1,'', '') AS STRING) AS col1 into #Temp1 FROM Table1';
      execute immediate ex_string without return;
      select col1 from #Temp1;
  end;;

• 0

  Comment actions Permalink

  

  Sebastian Klinnert

  • December 15, 2020 08:21

  Hi Leo,

  I created this thicket on you behalf, https://datavirtuality.zendesk.com/agent/tickets/20145712.

  We can further discuss in this ticket.

  best
  Sebastian

Please sign in to leave a comment.