Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005
    Posts
    4

    Unanswered: SQL Stored Procedure error

    Please find below the SQL Stored Procedure:

    CREATE PROCEDURE SAMPLE_PROC(IN arg1 CHAR(7),
    IN arg2 CHAR(6),
    IN arg3 INTEGER,
    IN arg4 VARCHAR(15),
    IN arg5 INTEGER)
    LANGUAGE SQL


    P1: BEGIN

    DECLARE var1 CHAR(6) DEFAULT NULL;
    DECLARE var2 CHAR(6) DEFAULT NULL;
    DECLARE var3 INT DEFAULT 0;
    DECLARE var4 INT DEFAULT 0;
    DECLARE var6 INT DEFAULT 0;
    DECLARE var5 INT DEFAULT 0;
    DECLARE var7 INT DEFAULT 0;
    DECLARE var8 INT DEFAULT 0;
    DECLARE var9 INT DEFAULT 0;
    DECLARE var10 INT DEFAULT 1; -- TODO:
    DECLARE var11 INT DEFAULT 1; -- TODO:
    DECLARE var12 INT;
    DECLARE var13 BIGINT;
    DECLARE var14 BIGINT;
    DECLARE var15 BIGINT;
    DECLARE var16 CHAR(6);
    DECLARE var17 CHAR(6);
    DECLARE var18 INT ;
    DECLARE var19 BIGINT;
    DECLARE var20 INT;
    DECLARE var21 INT;
    DECLARE var22 INT;
    DECLARE var23 INT DEFAULT 351;
    DECLARE var24 INT DEFAULT 352;
    DECLARE var25 INT DEFAULT 0;
    DECLARE var26 INT DEFAULT 0;

    IF arg5 = 1 THEN
    SET var12 = var10;
    ELSEIF arg5 = 2 THEN
    SET var12 = var11;
    END IF;

    SELECT com.AmountOf1Month,
    com.AmountOf3Months,
    com.AmountOf6Months,
    com.NextBuyingYM,
    com.BuyingYM,
    com.BuyingDuration,
    com.CommuteAllowance,
    com.VehicleTypeCd,
    val.SpecialAssignTypeCd,
    val.MgrCourseTitleCd
    INTO
    var13,
    var14,
    var15,
    var16,
    var17,
    var18,
    var19,
    var20,
    var21,
    var22
    FROM M_PACommuteAllowance com,
    V_AgentLatestJobTitle val
    WHERE com.PACd = arg1
    AND com.PACd = val.PACd
    AND com.PaymentTerm = arg2;


    IF arg3 IN(var23, var24) THEN
    IF var21 = var25 THEN --TODO: NEED TO CHECK
    SET var8 = 0;
    ELSE
    SET var8 = var13;
    SET var5 = var13;
    END IF;

    SET var26 = F_SCL_993(arg1, arg2);

    IF var26 = 1 THEN
    SET var1 =
    getYMFromDate(getFirstDayFromYM(arg2) + 3 MONTHS);
    SET var2 = arg2;
    SET var3 = 3;
    SET var4 = var14;
    SET var9 = var14;
    ELSE
    SET var1 =
    getYMFromDate(getFirstDayFromYM(arg2) + 1 MONTH);
    SET var2 = NULL;
    SET var3 = 0;
    SET var4 = 0;
    SET var9 = 0;
    END IF;
    END IF;

    END P1



    I got the following error:

    DB2ADMIN.SAMPLE_PROC - Build started.
    Create stored procedure returns -101.
    DB2ADMIN.SAMPLE_PROC: 105: [IBM][CLI Driver][DB2/NT] SQL0101N The statement is too long or too complex. LINE NUMBER=105. SQLSTATE=54001

    DB2ADMIN.SAMPLE_PROC - Build failed.
    DB2ADMIN.SAMPLE_PROC - Roll back completed successfully.


    Thanks,
    Rohit

  2. #2
    Join Date
    Apr 2004
    Posts
    54
    Update Db Cfg Using Stmtheap 2048

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •