Im passing table name & column name which im selecting from cursor.But its inserting correctly and also showing error like cursor is not open .
my proc is as:
my proc is as:
Code:
CREATE OR REPLACE PROCEDURE TEST_BUILDINGCODE_PROC (IN v_AppId_Ver INTEGER)
BEGIN
DECLARE v_ObjId,v_LayerName,v_TableName,v_ColumnName varchar(30);
DECLARE v_CodeNo,v_DataCount integer;
DECLARE v_Statement,v_Schema,v_Counter varchar(200);
DECLARE v_stmt STATEMENT;
DECLARE C1 CURSOR WITH RETURN FOR
SELECT IDVER,LAYERNAME,CODENO,TABLENAME,COLUMNNAME FROM MPD2021.DA_TEST_LAYERCODE WHERE IDVER=v_AppId_Ver;
SET v_Schema='MPD2021';
SET v_DataCount=(SELECT COUNT(*) FROM MPD2021.DA_TEST_LAYERCODE WHERE IDVER=v_AppId_Ver);
SET v_Counter=1;
OPEN C1;
WHILE(v_Counter<=v_DataCount)
DO
FETCH C1 INTO v_ObjId,v_LayerName,v_CodeNo,v_TableName,v_ColumnName;
--SET v_Schema='KKD';
SET v_Statement ='INSERT INTO ' || v_Schema || '. '|| v_TableName ||'(IDVERSION,'||v_ColumnName||') VALUES('||v_AppId_Ver||','||v_CodeNo||')';
--set v_Statement='INSERT INTO MPD2021.' concat v_Schema concat ' VALUES ( 'concat ' 4 ' concat ')';
--SET v_Statement = 'INSERT INTO ' || v_Schema || '. '|| v_TableName ||'('||v_ColumnName||') VALUES('||3||')';
PREPARE v_stmt FROM v_Statement;
EXECUTE v_stmt;
COMMIT;
--INSERT INTO TabName (IDVERSION,OBJID,LAYER,v_ColumnName) VALUES(v_AppId_Ver,v_ObjId,v_LayerName,v_CodeNo);
-- SET v_DataCount=v_DataCount-1;
SET v_Counter = v_Counter + 1;
END WHILE;
CLOSE C1;
END