I have written the following stored procedure using IBExpert
educational edition to dynamically create a string to be executed as
a select statement. Here is the code:
SET TERM ^ ;

CREATE PROCEDURE CATALOGUE_SEARCH (
S_PROVIDER VARCHAR(100),
S_NAME VARCHAR(50),
S_LOCATION VARCHAR(30),
S_KEYWORD VARCHAR(400))
AS
DECLARE VARIABLE Y VARCHAR(1) = 'Y';
DECLARE VARIABLE VAR_SEARCHSTRING VARCHAR(1000) = 'Select * from
TBL_Catalogue where COURSETITLE <>'''; /* the complete select
statement */
BEGIN
IF (S_PROVIDER <> 'any') then
BEGIN
VAR_SEARCHSTRING = VAR_SEARCHSTRING || ' AND
PROVIDERINSTITUTION = '||:s_provider ;
END
IF (S_NAME <> 'any') THEN
BEGIN
VAR_SEARCHSTRING = VAR_SEARCHSTRING || ' AND ENTRYID IN
(select ENTRYID from TBL_LKCATEGORY where CHECKED = '||:y||' AND
CATEGORYID IN (select CATEGORYID from TBL_CATEGORIES where NAME
= '||:s_name||'))';
END
IF (S_LOCATION <>'any') THEN
BEGIN
VAR_SEARCHSTRING = VAR_SEARCHSTRING || ' AND ENTRYID IN
(SELECT ENTRYID FROM TBL_MODULE_DELIVERY Where DELIVERYTOWN
= '||:s_location||')';
END
IF (S_KEYWORD <> '') THEN
BEGIN
VAR_SEARCHSTRING = VAR_SEARCHSTRING || ' AND ENTRYID IN
(select ENTRYID FROM TBL_LKCATEGORY where CHECKED ='||:y||' AND
CATEGORYID IN (select CATEGORYID FROM TBL_CATEGORIES WHERE
TYPICALTOPICS LIKE "%'||:s_keyword||'%" OR DESCRIPTION
LIKE "%'||:s_keyword||'%"))';
END
IF (VAR_SEARCHSTRING <> '') then
BEGIN
VAR_SEARCHSTRING = VAR_SEARCHSTRING || ' ORDER BY
COURSETITLE;' ;
execute statement (VAR_SEARCHSTRING);
END
END
^

SET TERM ; ^

DESCRIBE PARAMETER S_PROVIDER PROCEDURE CATALOGUE_SEARCH
'The provider institution from catalogue table';

DESCRIBE PARAMETER S_NAME PROCEDURE CATALOGUE_SEARCH
'The Name field in tbl_categories';

DESCRIBE PARAMETER S_LOCATION PROCEDURE CATALOGUE_SEARCH
'DeliveryTown in module delivery table';

DESCRIBE PARAMETER S_KEYWORD PROCEDURE CATALOGUE_SEARCH
'random keyword input by user';

GRANT EXECUTE ON PROCEDURE CATALOGUE_SEARCH TO SYSDBA;

Everything compiles fine but the execute statement does not work.
The error messages are:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Unexpected end of command.

and in debug mode:

Error Message:
----------------------------------------
SQL Parse Error:

EOF in string detected

I am using the IBExpert educational edition when compiling this
stored procedure.

I'm a student attempting this for a final year project so any help
would be appreciated.