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