Create a stored processes including parameter

subrat1 — Mon, 27 Jun 2016 14:51:49 GMT

I want to create stored process in SAS Enterpriseguide for the following oracle sql code, kindly tell me how to write stored process for it?

here there are two parameter :1)start year 2)end year

create or replace
PROCEDURE AUTO_PREMIUM_INDICATION
(
START_YEAR IN NUMBER
, END_YAER IN NUMBER
) AS
SOURCE_SYSTEM VARCHAR(500) := '(2,3,4,14,15)';
POLICY_TYPE VARCHAR(500):= '(1,15)';

BEGIN

    --STEP1: GET COVERAGE INFO
    --DELETE IF TABLE EXIST
    DELOBJECT ('tcoverages_summary','TABLE');

    EXECUTE IMMEDIATE ('create table tcoverages_summary as
    SELECT /*+ PARALLEL */
          POLICY_TYPE_SK,
            source_system_sk,
            ACTUAL_DT,
            EXTRACT(YEAR FROM ACTUAL_DT) as YEAR_CT,
            EXTRACT(MONTH FROM ACTUAL_DT) as MONTH_CT,
            INTCOV_SK,
            policy_sk,
            item_attribute_sk,
            --BROKER_NM,
            EARNED_XPSR_AM,
            WRITTEN_XPSR_AM,
            MTD_WRITTEN_PRM_AM,
            EARNED_PRM_AM
    FROM                 TCOVERAGES_SUMMARY@MISP.AIG.COM T1
    LEFT JOIN
            Tdate@MISP.AIG.COM T2
    on
            (T1.MONTH_SK = T2.DATE_SK)
    /*LEFT JOIN
    --        TBROKER_DIMENSION@MISP.AIG.COM T3
    ON
            T3.BROKER_SK = T1.bROKER_SK*/
    WHERE   POLICY_TYPE_SK IN '|| POLICY_TYPE ||' and
            source_system_sk NOT IN '|| SOURCE_SYSTEM ||' AND
            SNAPSHOT_TYPE_IN = 0 AND
            EXTRACT(YEAR FROM ACTUAL_DT)>= '||START_YEAR||' AND
            EXTRACT(YEAR FROM ACTUAL_DT)<= '||END_YAER||'');

Re: Create a stored processes including parameter

LinusH — Wed, 29 Jun 2016 13:14:43 GMT

The whole set up seems a bit awkward. Creating a SAS Stored Process that creates/calls a Stored Procedure, that does...what?

Elaborate about the use case so we can can give adequate directions.

And if we look from the other direction, what do you know about stored process, and not? What have you tried so far?

Perhaps start up with a simple "hello world" example, and take it from there...

topic Create a stored processes including parameter in Developers

Create a stored processes including parameter

Re: Create a stored processes including parameter