Stored Procedure Error

pts.
Tags:
Application software
Availability
DB2 Everyplace
Development
IBM
Instant Messaging
Java
Migration
Modeling
Normalization
Performance/Tuning
Servers
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..

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ARWinner
    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
    0 pointsBadges:
    report
  • GRowl
    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;
    0 pointsBadges:
    report
  • GunaPat
    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
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following