Creating Dynamic SQL in Stored Procedure in Content Section

[ketan_pavle]

Can someone help me in creating a Dynamic SQL in the content Section.

Please refer to this Q and A for creating dynamic SQL in catalog section

https://answers.sap.com/questions/123794/sap-dbtech-jdbc-359-string-is-too-long-359-range-3.html

I will try to explain the issue in detail

Currently we have 19 tables in our schema (in a particular format)

0ZSSM6A, 0ZSSM6B, …. 0ZSSM6K, 0ZSSM6L, 0ZSSM7A, 0ZSSM7B etc. where 6 is the year (2016, 2017) and A( Jan), B(feb), C… L (dec) is the month.

We need to do a union all on all these tables. It will grow every next month and probably old tables might get dropped after few years.

I was able to create a SP in catalog section to dynamically unionall all the tables dynamically.

But I am not able to create a same kind of SP in the content section. As per my knowledge( after reading several blogs), content section Procedures doesn’t support DYNAMIC SQLs.

IS there a solution or a workaround to resolve this issue.

Our goal is to create a cal view using this stored proc.

Thanks. Appreciate all the help.

Ketan

[lbreddemann]

Why do you think that design time procedures don't allow dynamic SQL?

Following the example I gave in the other thread, this is the "design time" version:

PROCEDURE "DEVDUDE"."sandpit::testdyn" ( ) 
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
    DEFAULT SCHEMA devdude
-- READS SQL DATA
 AS
BEGIN
/***************************** 
Write your procedure logic 
 *****************************/
 declare sqlcmd nvarchar(5000) :='';
 declare cursor mytabs for 
    select  to_nvarchar('(select * from "' || schema_name || '"."' || table_name || '")') as seltext
    from tables
    where table_name like 'CUSERS_';
 for c as mytabs do
    if sqlcmd = '' then
        sqlcmd := c.seltext;
    else
        sqlcmd := :sqlcmd ||' UNION ALL ' || c.seltext ;
    end if;
 end for;
 select sqlcmd from dummy;
 execute immediate :sqlcmd;
END;

As you see, all I did was commenting out the "READS SQL DATA" phrase, which basically means "read-only".