Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    16

    Unanswered: "SIN"tatic Prodecure

    Here is procedure which does its work perfectly a number is given to the where clause
    but here i wanted to generate that value from a sql select query(10th line) and give it to the declaration cursor
    how do i do it?
    DELIMITER $$
    DROP PROCEDURE IF EXISTS `DEV`.`spClaimUserArchive`$$
    CREATE PROCEDURE `spClaimUserArchive`()
    BEGIN
    DECLARE vClaim varchar(15);
    DECLARE vRefUserId varchar(15);
    DECLARE vClaimEntered char(1);
    DECLARE vbatch varchar(15) ;
    DECLARE no_more_rows BOOLEAN;


    select max(Batch) into vbatch from tblClaimUser ;
    DECLARE curClaimUserArchive CURSOR FOR select Claim,RefUserId,ClaimEntered from tblClaimUserArchive where Batch=vbatch ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND

    SET no_more_rows = TRUE;


    OPEN curClaimUserArchive;

    curClaimUserArchive_loop: LOOP

    FETCH curClaimUserArchive into
    vClaim,vRefUserId, vClaimEntered;
    IF no_more_rows THEN
    insert into tblClaimUserArchive select * from tblClaimUser ;
    truncate table tblClaimUser;

    CLOSE curClaimUserArchive;

    LEAVE curClaimUserArchive_loop;

    END IF ;
    delete from tblClaimUserArchive where Batch=vbatch ;
    END LOOP curClaimUserArchive_loop;
    CLOSE curClaimUserArchive;
    END
    $$
    DELIMITER ;
    Last edited by skarri; 06-08-09 at 08:48.
    "TALK SENSE TO FOOL. HE CALLS U FOOLISH"

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Why don't you tell us what it is you're trying to do rather than let us try and work it out by going through all your code. My initial guess is that if you're using cursors then you're doing it the wrong way anyway.

Posting Permissions

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