Dynamic SQL Execution from Temp Table

[Former Member]

Dear Experts,

I have some scenarios where i have to run some sql queries dynamically , i went through many threads and found some useful and tried to use them but unfortunately i am still not done with this.

below is my code i want to execute

Regards,

Manish

drop procedure DNsql;
CREATE PROCEDURE DNsql (out  Account nvarchar(200))
LANGUAGE SQLSCRIPT
AS
  SQL1 nvarchar(200) := '';
BEGIN
create LOCAL TEMPORARY TABLE "#temp" (Accnt nvarchar(200));
SQL1:='insert into "#temp" SELECT 'SELECT'||'  '||' '|| '"Account"' ||' '||'FROM'||' '||SCHEMA_NAME||'.'||'JDT1' 
FROM schemas WHERE SCHEMA_NAME LIKE 'SBK%%%%'';
exec SQL1;
select  Accnt into Account from "#temp";
DROP TABLE "#temp";
END;

[lbreddemann]

Instead of looking for a workaround with dynamic SQL, can you explain what you actually try to do here?

Why do you copy data into a temp. table first?

Why are there multiple schemas, with apparently identical tables that you can simply access from one user?

[Former Member]

Hi Lars,

am trying to pull data from multiple schemas without using any Union/Union all , as i explained you in my previous thread that schemas will be keep coming based on new project for which client wants to maintain it as a separate entity , but all schemas will be having same object types and tables . so now client wants the reports ex: Balance sheet, P&L etc for all the projects i.e. schemas which falls under the same company group. now the challenge is, if i do something like union all the schemas and after some time a new schema comes in then we have to edit the code again and again.

let me know if you need some more clarification.

Regards,

Manish

[lbreddemann]

Ok, I'm not really keeping track of everyone's design approaches - I'm answering questions in three different forums with overlapping user groups, so I really only take a question by itself.

As for the design approach: I wouldn't want to go and collect data from schemas into a temp table and then work from there. That's literally taking all efforts to kill performance.

Instead, what you want to automate is the creation of your reporting layer. Why don't you rather create the required code to onboard/offboard specific schemas? This code could take care of extending/reducing views, handling privileges etc.

That'll be much more maintainable and flexible.

Practically speaking you're dealing with datamarts here. And that's something you absolutely should automate as much as possible.