PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/28/2017 4:26 PM by  dcasper70
Dynamically change SQL Proc in IPD?
 1 Replies
Sort:
You are not authorized to post a reply.
Author Messages
dcasper70
Sr Systems Analyst
Private
Basic Member
(13 points)
Basic Member
Posts:7


Send Message:

--
11/22/2017 11:43 AM

    IPD v11.0.1.43,  single tenant, cloud, SQL Server. 

    Business has about a dozen or so bonus programs.  Each program has different criteria, run dates/frequencies, and payout amounts. 

    We're attempting to use a single flow to create the EmployeeSpecialIncentivePayout (ESIP) business class records for the desired program.  We figure that all the ESIP entries can be done in one Landmark transaction node, and all that we need to vary is the SQL that runs to get the people and payouts and other ESIP data.  Some of the SQL is pretty complex, so SQL Procedures would be our preferred method.

    We set up multiple triggers in rich client, each one on it's own schedule, all calling the same flow.  They all contain a variable called 'BonusType' that hold which sql proc to call.    
    So the PfiTriggerVariable table looks like this:
    1 BonusType NewHire
    2 BonusType Longevity
    3 BonusType GiveDave500Bucks

    We then created multiple SQL Procs that have identical output formats:
    lm_ghr.spGetBonusNewHire
    lm_ghr.spGetBonusLongevity
    lm_ghr.spGetBonusGiveDave500Bucks
    you get the picture...

    Within the flow, we created a vSQL variable, grabed that 'BonusType' value and then in an assign node, make vSQL= "CALL PROC lm_ghr.spGetBonus" + BonusType.

    Our hope was to then put that variable into the SQL node's 'Run SQL Procedure' section and voila!  Dynamically call the correct proc for the triggered bonus.  We could then leave the bonus processing flow alone and add/remove bonuses through the rich client triggers, creating the corresponding SQL procs as needed.  
    Seemed good on paper....

    In the SQL node, the 'Run SQL Query' allows the use of variables, but the 'Run SQL Procedure' does not seem to. I'd rather not create a separate SQL node for each program.  That would seem to defeat the purpose of this whole attempt.

    Can this idea even be accomplished?  Is there a setting I'm overlooking?  Another way to accomplish the same goal?  Any thoughts or workarounds are welcome.

    dcasper70
    Sr Systems Analyst
    Private
    Basic Member
    (13 points)
    Basic Member
    Posts:7


    Send Message:

    --
    11/28/2017 4:26 PM

    Well, we opened up a xtreme ticket and are waiting for them to finish 'researching'.  In the meantime, we figured out a solution...   Still testing it, but results so far are favorable!  Figured I'd share.  Any feedback is appreciated.

    So we have all the specific triggers with their variables set up in rich client, and created the SQL Procs for each individual bonus program, just the way we laid out in the prior post.

    We created an additional shell procedure with one parameter (BonusType).  Inside that proc we build an execute query string and call sp_executesql.  

    All we have to do in the flow is pull the trigger value, assign it to a variable, and provide it as the parameter to the shell proc below.

    --------------------------------------------------------------------------------------------

    create procedure lm_ghr.spBonusShell @BonusType as NVARCHAR
    AS

    BEGIN
    SET NOCOUNT ON;

       DECLARE @queryToRun NVARCHAR(128) = 'EXECUTE lm_ghr.spBonus' + @BonusType;

       EXECUTE sp_executesql @queryToRun;

    END

    --------------------------------------------------------------------------------------------

     

    If we need to add a bonus program, we can just create the process trigger with the BonusType variable, and create the SQL proc it needs to run.  As long as all the bonus SQL statements return data in a consistent layout, the main flow doesn't need to be touched.

    You are not authorized to post a reply.