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 GetSearchedRecords (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(SqlSplitDelim(pKeyWords)) ur;


IF (pLimit = 0) THEN

SET vTopLimit = ' ';

ELSE

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

END IF;


SET
vsqlQuery = 'Select ' || vTopLimit || ' rec.RecordID, rec.Subject, fol.FolderName, rec.ReportDate, us.FirstName ||''|| us.LastName as Source,
field1.Field1TypeName, field2.Field2TypeName, field3.Field3TypeName, substr(rec.Comments, 1, 50) as Comments,
(Select Count(FileName) From Attachments Where DocumentID = rec.RecordID) As AttachmentCount
From ((((Records rec Left Join Field1Types field1 On rec.Field1TypeID = field1.Field1TypeID)
Left Join Field2Types field2 On rec.Field2TypeID = field2.Field2TypeID)
Left Join Field3Types field3 On rec.Field3TypeID = field3.Field3TypeID)
Inner Join Names_Users us On rec.CreatorUserID = 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_GetFolderAccessTypeID(fol.FolderID,''' || CAST (pUserID AS VARCHAR(100)) || ''') > 1 ';

SET vkeywordCount = (SELECT COUNT(ur.value)
FROM table(SqlSplitDelim(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.field1typename like ''%' || vkey || '%'' or field2.field2typename like ''%' || vkey || '%''or field3.field3typename 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.field1typename like ''%' || vkey || '%'' or field2.field2typename like ''%'|| vkey || '%'' or field3.field3typename 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.ModifiedDate ';

ELSE

SET vDateTypeColumn = ' ';

END IF;

END IF;

END IF;

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


IF (vDateTypeColumn <> ' ') 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 (pFolderCategory = 1
OR pFolderCategory = 2) THEN

SET vsqlQuery = vsqlQuery || ' and ';


IF (pFolderCategory = 1) THEN


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

ELSE

IF (pFolderCategory = 2) THEN


SET vsqlQuery = vsqlQuery || ' rec.FolderID In
(Select Value From F_GetDescendantsFolderID(''' || 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.