Home > IT Answers > DataCenter > Stored Procedure Error
Help

Question

  Asked: Dec 17 2004   2:20 AM GMT
  Asked by: jsbaig

Stored Procedure Error


Application software, IBM, Servers, Availability, Migration, Modeling, Normalization, Performance/Tuning, DB2 Everyplace, Instant Messaging, Development, Java, WebSphere Studio

This is mu stored Procedure:

CREATE PROCEDURE ADMINISTRATOR.DIMS_SP_PerformSearch
(
vuserId integer,
vsearchText varchar(500) ,
vfromID integer,
voption integer
)
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN
IF (vsearchText != '') THEN
delete from DIMS_General_Search where userId = vuserId;
end if;
IF (voption = 0) THEN

INSERT INTO DIMS_General_Search
SELECT vuserId,DIMS_Scanned_Transaction.scanned_date , DIMS_Document_Type.document_type_name , DIMS_Document_type_Attributes.attribute_name,DIMS_Scanned_transaction_Detail.answer, DIMS_Scanned_Transaction.scanned_transaction_id
FROM DIMS_Scanned_Transaction , DIMS_Document_Type , DIMS_Document_type_Attributes , DIMS_Scanned_transaction_Detail
WHERE DIMS_Scanned_Transaction.scanned_transaction_id in (
SELECT scanned_transaction_id FROM DIMS_Scanned_transaction_detail WHERE answer like '%' || vsearchText || '%')
and DIMS_Scanned_Transaction.document_type_id = DIMS_Document_Type.document_type_id
and DIMS_Scanned_transaction_Detail.scanned_transaction_id = DIMS_Scanned_transaction.scanned_transaction_id
and DIMS_Scanned_transaction_Detail.document_type_attribute_id = DIMS_Document_type_Attributes.document_type_attribute_id
order by DIMS_Document_Type.document_type_name;

--- SELECT * FROM DIMS_General_Search WHERE userId = 1 order by documentName , generalSearchId FETCH FIRST 30 Rows Only;
ELSE
IF (voption > 1) THEN
SELECT generalSearchId,userId,scannedDate,documentName,documentNo,attributeName,answer FROM DIMS_General_Search WHERE userId = vuserId
GROUP By generalSearchId,userId,scannedDate,documentName,documentNo,attributeName,answer
HAVING generalSearchId > vfromID
order by documentName , generalSearchId
FETCH FIRST 21 Rows Only;
ELSE
IF (voption < 0) THEN
SELECT generalSearchId,userId,scannedDate,documentName,documentNo,attributeName,answer FROM DIMS_General_Search WHERE userId = vuserId
GROUP By generalSearchId,userId,scannedDate,documentName,documentNo,attributeName,answer
HAVING generalSearchId <= vfromID
order by documentName , generalSearchId;
FETCH FIRST 21 Rows Only
END IF;
END If;
END If;
END

On Compilation it gives this Error

ADMINISTRATOR.DIMS_SP_PerformSearch: 31: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "SELECT generalSearchId,userId,sca" was found following "on > 1) THEN ". Expected tokens may include: "<psm_sql_stmt>". LINE NUMBER=31. SQLSTATE=42601

Please Suggest what's the problem..

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Dec 17 2004  6:11 AM GMT  by dejabu




I am not able to run this but purely looking at the syntax it may be as simple as requiring a ';' after your final FETCH statement.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on DataCenter, Microsoft Windows and Oracle.

Looking for relevant DataCenter Whitepapers? Visit the SearchDataCenter.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

ARWinner  |   Dec 17 2004  8:11AM GMT

The problem is that a SELECT statement all by itself in a Stored Procedure (SP) is meaningless and therefore not allowed. DB2 does not know what you intend to do with the result set, i.e. there is no indication on your part whether you want to do some processing within the SP on the result set, or you wish to have the calling procedure/application receive the result set. To do this you need to use a cursor. If you want to process the result set in the SP you would use OPEN CURSOR, and FETCH in a loop, then CLOSE it. If you want the result set returned to the caller, declare the cursor as such and then you OPEN it and leave it open for the caller to use.

Andy

 

GRowl  |   Dec 17 2004  12:16PM GMT

It appears that you meant to remark a line and did not:

— SELECT * FROM DIMS_General_Search WHERE userId = 1 order by documentName , generalSearchId FETCH FIRST 30 Rows Only;

 

GunaPat  |   Dec 17 2004  12:38PM GMT

You can’t use direct sql in the stored procedure. You need to declare a cursor for the sql statment, open and fetch the cursor to process the result set.

For Example, in the stored proc you need to the following to get the resutl set,
declare cur_name cursor with return for
select statement ….;
open cur_name;

If you need to process individual rows then you need to fetch otherwise open cur_name will return the result set as you expected.

Make sure in the create procedure it has a specification to return one result set.
Create procedure proc_name()
result sets 1
language sql
p1:begin

end p1

Hope this helps.

Thanks
Guna