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

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.


Add comment
10|10000 characters needed characters exceeded

1 Answer

  • Posted on 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" ( ) 
        DEFAULT SCHEMA devdude
    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;
            sqlcmd := :sqlcmd ||' UNION ALL ' || c.seltext ;
        end if;
     end for;
     select sqlcmd from dummy;
     execute immediate :sqlcmd;

    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