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?
Quote:
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 ;
|
dBforums
>
"SIN"tatic Prodecure
06-08-09, 07:09
Linear Mode
