Skip to Content

Creating Dynamic SQL in Stored Procedure in Content Section

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 20, 2017 at 08:21 AM

    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".

    Add comment
    10|10000 characters needed characters exceeded