4
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
