Results 1 to 8 of 8

Thread: Stored Proc

  1. #1
    Join Date
    Oct 2008
    Posts
    19

    Unanswered: Stored Proc

    Hi all,

    I would like to write a stored proc that takes a IN parameter(s) and then uses this to dynamically change the WHERE clause of my sql.
    example is something like:

    CREATE PROCEDURE BLAH(IN MYVAR VARCHAR(50),IN MYVAL VARCHAR(50))

    ....
    ....
    DECLARE ADDWHERE VARCHAR(100);
    IF MYVAR='VIEWCAT' THEN
    SET ADDWHERE = 'TABLE1.FIELD1='''|| MYVAL || '''';
    ELSE IF MYVAR='VIEWDOG' THEN
    SET ADDWHERE = 'TABLE1.FIELD2='''|| MYVAL || '''';
    END IF;

    DECLARE C1 CURSOR FOR
    'SELECT * FROM TABLE1 WHERE ' || ADDWHERE;

    ----------

    Is this possible? I've tried and can't get it to work. Not sure that the single quotes around the SELECT are correct and if I'd need to use some kind of PREPARE statement - just don't know. Any ideas?


    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You will need to set it up using dynamic SQL. You will need to set a varchar variable to the entire SQL statement you want to execute. Then you will prepare it and execute it. There are numerous examples. Look in the documentation on PREPARE and OPEN.

    Andy

  3. #3
    Join Date
    Dec 2002
    Posts
    123
    Here's an example ---

    After all your declare statements including
    DECLARE stmt VARCHAR(1000);-- give something like this :

    SET stmt = 'SELECT val FROM SESSION.TmpValues WHERE id = ?
    ORDER BY readingval DESC FETCH FIRST 3 ROWS ONLY ';

    SET hasNext = 1;
    PREPARE s1 FROM stmt;
    BEGIN
    DECLARE totalDateTime TIMESTAMP;

    DECLARE c1 CURSOR FOR s1;
    OPEN c1 USING sid;
    FETCH c1 INTO totalDateTime;

    WHILE hasNext = 1 DO

    SET endTime = totalDateTime;
    SET startTime = endTime - 1 DAY;

    CALL COLLECT_DATA(param1, param2, param3 );
    FETCH c1 INTO totalDateTime;
    END WHILE;
    CLOSE c1;
    END;

  4. #4
    Join Date
    Oct 2008
    Posts
    19
    The stored proc is to be used for a Crystal Report so I just want to return a RESULTSET, not FETCH the results or anything like that. I want to put the result of the query into a cursor and then open the cursor.
    I tried this but it didn't work:

    CREATE PROCEDURE BLAH(IN MYVAR VARCHAR(50),IN MYVAL VARCHAR(50))

    ....
    ....
    DECLARE ADDWHERE VARCHAR(100);
    DELCARE SMT VARCHAR(1000);

    IF MYVAR='VIEWCAT' THEN
    SET ADDWHERE = 'TABLE1.FIELD1='''|| MYVAL || '''';
    ELSE IF MYVAR='VIEWDOG' THEN
    SET ADDWHERE = 'TABLE1.FIELD2='''|| MYVAL || '''';
    END IF;

    SET SMT = 'SELECT * FROM TABLE1 WHERE ' || ADDWHERE;

    PREPARE S1 FROM SMT;
    DECLARE C1 CURSOR FOR S1;

    OPEN C1;
    -----

    Any reason why this won't work?

    Thanks again.

  5. #5
    Join Date
    Dec 2002
    Posts
    123
    Two things --
    Change ELSE IF to ELSEIF (one word)

    and secondly change the cursor part to this --

    PREPARE S1 FROM SMT;

    BEGIN
    DECLARE C1 CURSOR FOR S1;
    OPEN C1;
    END;

    It should work now..

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This is an answer to the OP which I posted on "Developers work for Linux, UNIX, and Windows".

    Code:
    SELECT *
      FROM TABLE1
     WHERE
      (    MYVAR =  'VIEWCAT'
       AND FIELD1 = MYVAL
      )
      OR
      (    MYVAR <> 'VIEWCAT'
       AND FIELD2 = MYVAL
      )
    ;

  7. #7
    Join Date
    Oct 2008
    Posts
    19
    Yes tonkuma, thanks for that. Looks a lot easier.
    Was also just interested in how to do it using PREPARE statement.

    DB2user - thanks again.
    One final thing, are you saying that I remove the IF THEN ELSEIF section from the BEGIN - END ?

    ie, something like:


    CREATE PROCEDURE BLAH(IN MYVAR VARCHAR(50),IN MYVAL VARCHAR(50))

    DECLARE ADDWHERE VARCHAR(100);
    DELCARE SMT VARCHAR(1000);

    IF MYVAR='VIEWCAT' THEN
    SET ADDWHERE = 'TABLE1.FIELD1='''|| MYVAL || '''';
    ELSEIF MYVAR='VIEWDOG' THEN
    SET ADDWHERE = 'TABLE1.FIELD2='''|| MYVAL || '''';
    END IF;


    SET SMT = 'SELECT * FROM TABLE1 WHERE ' || ADDWHERE;

    --ALL OF ABOVE BEFORE THE BEGIN-END?

    BEGIN
    PREPARE S1 FROM SMT;
    DECLARE C1 CURSOR FOR S1;
    OPEN C1;
    END;

  8. #8
    Join Date
    Dec 2002
    Posts
    123
    You just need to put the cursor declaration in its own BEGIN/END BLOCK...so your final sproc should look like this..



    CREATE PROCEDURE BLAH(IN MYVAR VARCHAR(50),IN MYVAL VARCHAR(50))
    RESULT SETS 1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL

    BEGIN

    DECLARE ADDWHERE VARCHAR(100);
    DELCARE SMT VARCHAR(1000);

    IF MYVAR='VIEWCAT' THEN
    SET ADDWHERE = 'TABLE1.FIELD1='''|| MYVAL || '''';
    ELSEIF MYVAR='VIEWDOG' THEN
    SET ADDWHERE = 'TABLE1.FIELD2='''|| MYVAL || '''';
    END IF;


    SET SMT = 'SELECT * FROM TABLE1 WHERE ' || ADDWHERE;

    PREPARE S1 FROM SMT;

    --starting another block here
    BEGIN
    DECLARE C1 CURSOR FOR S1;
    OPEN C1;
    END;

    END
    @

    --- then just give this command to create the procedure...
    db2 -td@ -vf filename.sql
    Last edited by db2user; 10-04-08 at 00:44.

Posting Permissions

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