Errorr execute immediate

[lakon15]

Dear all,
I'll try to convert from SQL server Store Procedure to DB2 Store Procedure.
I've make SP under DB2 like this
CREATE PROCEDURE GetSearchedReco rds (pKeyWords VARCHAR(1000),
pIsAll INTEGER,
pIsExact INTEGER,
pDateCondition INTEGER,
pDateType INTEGER,
pDateOn TIMESTAMP,
pDateTo TIMESTAMP,
pLimit INTEGER,
pFolderCategory INTEGER,
pFolderID CHAR(32),
pSort INTEGER,
pUserID CHAR(32) )
LANGUAGE SQL

BEGIN


DECLARE vsqlQuery VARCHAR(8000);

DECLARE vTopLimit VARCHAR(30);

DECLARE voperator VARCHAR(5);

DECLARE vkey VARCHAR(300);

DECLARE vkeywordCount INTEGER;

DECLARE vloop INTEGER;

DECLARE l_sqlstatus INTEGER;

DECLARE vDateTypeColumn VARCHAR(20);

DECLARE vSortCondition VARCHAR(35);

DECLARE key_cursor CURSOR FOR SELECT ur.Value
FROM table(SqlSplitD elim(pKeyWords) ) ur;


IF (pLimit = 0) THEN

SET vTopLimit = ' ';

ELSE

SET vTopLimit = ' TOP ' || CAST (RTRIM(CHAR(pLi mit)) AS VARCHAR(30)) || ' ';

END IF;


SET
vsqlQuery = 'Select ' || vTopLimit || ' rec.RecordID, rec.Subject, fol.FolderName, rec.ReportDate, us.FirstName ||''|| us.LastName as Source,
field1.Field1Ty peName, field2.Field2Ty peName, field3.Field3Ty peName, substr(rec.Comm ents, 1, 50) as Comments,
(Select Count(FileName) From Attachments Where DocumentID = rec.RecordID) As AttachmentCount
From ((((Records rec Left Join Field1Types field1 On rec.Field1TypeI D = field1.Field1Ty peID)
Left Join Field2Types field2 On rec.Field2TypeI D = field2.Field2Ty peID)
Left Join Field3Types field3 On rec.Field3TypeI D = field3.Field3Ty peID)
Inner Join Names_Users us On rec.CreatorUser ID = us.UserID)
Inner Join Folders fol On fol.FolderID = rec.FolderID
';

IF (pIsAll = 0) THEN

SET voperator = ' or ';

ELSE

IF (pIsAll = 1) THEN

SET voperator = ' and ';

END IF;

END IF;

SET vsqlQuery = vsqlQuery || ' Where F_GetFolderAcce ssTypeID(fol.Fo lderID,''' || CAST (pUserID AS VARCHAR(100)) || ''') > 1 ';

SET vkeywordCount = (SELECT COUNT(ur.value)
FROM table(SqlSplitD elim(pKeywords) )ur) ;

IF (vkeywordCount <> 0) THEN

SET vsqlQuery = vsqlQuery || ' and (';

END IF;

SET vloop = 0;

OPEN key_cursor;

SET l_sqlstatus = 0;
FETCH FROM key_cursor INTO vkey;

WHILE l_sqlstatus = 0 DO

IF (vloop = vkeywordCount - 1) THEN

SET vsqlQuery = vsqlQuery || ' (rec.Subject like ''%' || vkey || '%'' or fol.foldername like ''%' || vkey || '%'' or us.FirstName like ''%' || vkey || '%'' or us.LastName like ''%' || vkey || '%'' or field1.field1ty pename like ''%' || vkey || '%'' or field2.field2ty pename like ''%' || vkey || '%''or field3.field3ty pename like ''%' || vkey || '%'' or rec.comments like ''%'|| vkey||'%'' ))';


ELSE


SET vsqlQuery = vsqlQuery || ' (rec.Subject like ''%' || vkey || '%'' or fol.foldername like ''%' || vkey || '%'' or us.FirstName like ''%' || vkey || '%'' or us.LastName like ''%' || vkey || '%'' or field1.field1ty pename like ''%' || vkey || '%'' or field2.field2ty pename like ''%'|| vkey || '%'' or field3.field3ty pename like ''%' || vkey || '%'' or rec.comments like ''%'|| vkey||'%'' )) ' || voperator ;



END IF;


SET vloop = vloop + 1;


SET l_sqlstatus = 0;
FETCH FROM key_cursor INTO vkey;

END WHILE ;


CLOSE key_cursor;




IF (pDateType = 0) THEN


IF (pDateCondition <> 0) THEN


SET vDateTypeColumn = ' rec.ReportDate ';

ELSE


SET vDateTypeColumn = ' ';

END IF;

ELSE

IF (pDateType = 1) THEN

SET vDateTypeColumn = ' rec.CreateDate ';

ELSE

IF (pDateType = 2) THEN


SET vDateTypeColumn = ' rec.ModifiedDat e ';

ELSE

SET vDateTypeColumn = ' ';

END IF;

END IF;

END IF;

IF (pDateCondition > 0
AND pDateCondition <= 4) THEN


IF (vDateTypeColum n <> ' ') THEN


SET vsqlQuery = vsqlQuery || ' and ';

IF (pDateCondition = 1) THEN


SET vsqlQuery = vsqlquery ||''|| vDateTypeColumn ||''|| '=' ||''|| Cast(pDateOn As varchar(11)) ;

ELSE

IF (pDateCondition = 2) THEN


SET vsqlQuery = vsqlquery ||''|| vDateTypeColumn ||''|| '>' ||''|| Cast(pDateOn As varchar(11)) ;


ELSE

IF (pDateCondition = 3) THEN


SET vsqlQuery = vsqlquery ||''|| vDateTypeColumn ||''|| '<' ||''|| Cast(pDateOn As varchar(11)) ;

ELSE


IF (pDateCondition = 4) THEN


SET vsqlQuery = vsqlquery ||''|| vDateTypeColumn ||''|| '>' ||''|| Cast(pDateOn As varchar(11)) ||''|| 'and'||''|| vDateTypeColumn ||''|| '<' ||''|| Cast(pDateOn As varchar(11)) ;


END IF;

END IF;

END IF;

END IF;

END IF;

END IF;

IF (pFolderCategor y = 1
OR pFolderCategory = 2) THEN

SET vsqlQuery = vsqlQuery || ' and ';


IF (pFolderCategor y = 1) THEN


SET vsqlQuery = vsqlQuery || ' rec.FolderID = ''' || CAST (pFolderID AS VARCHAR(100)) || '''';

ELSE

IF (pFolderCategor y = 2) THEN


SET vsqlQuery = vsqlQuery || ' rec.FolderID In
(Select Value From F_GetDescendant sFolderID(''' || CAST (pFolderID AS VARCHAR(100)) || ''')
Union Select ''' || CAST (pFolderID AS VARCHAR(100)) || ''')';

END IF;

END IF;

END IF;

IF (pSort = 0) THEN

SET vSortCondition = ' order by rec.FolderID ASC ';

ELSE

IF (pSort = 1) THEN


SET vSortCondition = ' order by rec.ReportDate DESC ';

ELSE

IF (pSort = 2) THEN

SET vSortCondition = ' order by rec.ReportDate ASC ';

ELSE

SET vSortCondition = ' ';

END IF;

END IF;

END IF;

SET vsqlQuery = vsqlQuery || vSortCondition;


EXECUTE IMMEDIATE vsqlQuery;

END

but when I try to execute this SP , giving error, An EXECUTE IMMEDIATE statement contains a SELECT or VALUES
statement. SQLSTATE=42612,
anyone help me please


Regards


Agus Wahyu

[Shashank1984]

The implied function is not supported.
Prepare the SELECT or VALUES statement.
Then use OPEN, FETCH, and CLOSE.

Thanks and Regards,
Shashank K.