Hello Steven,

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

s> CREATE PROCEDURE CATALOGUE_SEARCH (
s> S_PROVIDER VARCHAR(100),
s> S_NAME VARCHAR(50),
s> S_LOCATION VARCHAR(30),
s> S_KEYWORD VARCHAR(400))
1). Your stored procedure is going to return no results of the search.
May be you should add RETURNS(<list of output parameters>) here. (You
cannot return a whole result set as in MSSQL, in FB you return
row-by-row.)

s> AS
s> DECLARE VARIABLE Y VARCHAR(1) = 'Y';
s> DECLARE VARIABLE VAR_SEARCHSTRING VARCHAR(1000) = 'Select * from
s> TBL_Catalogue where COURSETITLE <>'''; /* the complete select
s> statement */
2). You'd better to specify all fields explicitly instead of * to
assign the field values to output parameters.
3). Dou you mean "COURSETITLE is not empty string"? If yes, you have
to double single quotes, that is 'select ... COURSETITLE<>'''''; (in
the above statement you just "opened the string", but not "closed").

s> BEGIN
s> IF (S_PROVIDER <> 'any') then
s> BEGIN
s> VAR_SEARCHSTRING = VAR_SEARCHSTRING || ' AND
s> PROVIDERINSTITUTION = '||:s_provider ;
s> END
s> IF (S_NAME <> 'any') THEN
s> BEGIN
s> VAR_SEARCHSTRING = VAR_SEARCHSTRING || ' AND ENTRYID IN
s> (select ENTRYID from TBL_LKCATEGORY where CHECKED = '||:y||' AND
s> CATEGORYID IN (select CATEGORYID from TBL_CATEGORIES where NAME
s> = '||:s_name||'))';
s> END
s> IF (S_LOCATION <>'any') THEN
s> BEGIN
s> VAR_SEARCHSTRING = VAR_SEARCHSTRING || ' AND ENTRYID IN
s> (SELECT ENTRYID FROM TBL_MODULE_DELIVERY Where DELIVERYTOWN
s> = '||:s_location||')';
s> END
s> IF (S_KEYWORD <> '') THEN
s> BEGIN
s> VAR_SEARCHSTRING = VAR_SEARCHSTRING || ' AND ENTRYID IN
s> (select ENTRYID FROM TBL_LKCATEGORY where CHECKED ='||:y||' AND
s> CATEGORYID IN (select CATEGORYID FROM TBL_CATEGORIES WHERE
s> TYPICALTOPICS LIKE "%'||:s_keyword||'%" OR DESCRIPTION
s> LIKE "%'||:s_keyword||'%"))';
s> END
4). As Helen mentioned, use single quotes for strings in SQL. Double
quotes are used for SQL case-sensitive name identifiers (names of
tables/fields/...).

s> IF (VAR_SEARCHSTRING <> '') then
s> BEGIN
s> VAR_SEARCHSTRING = VAR_SEARCHSTRING || ' ORDER BY
s> COURSETITLE;' ;
s> execute statement (VAR_SEARCHSTRING);
s> END
5). Finally, as I can imagine, you have to return rows that match the
user criteria, back to that user. So, you have to rewrite the execute
statement as
-----
for execute statement VAR_SEARCHSTRING into :OutputPar1,... do
suspend;
-----
Then you can perform something like
-----
select * from CATALOGUE_SEARCH ('provider', 'name', 'location',
'keyword');
-----
to get the result set.

s> END
s> ^

s> SET TERM ; ^

s> Everything compiles fine but the execute statement does not work.
At compile time FB cannot perform syntax check for EXECUTE STATEMENT
argument (see FB 1.5 release notes or SQL extentions).

And usually it's more efficient to use EXISTS(...) instead of
IN(select...) (search the list archive for some examples).


HTH
--
Best regards,
Pavel Menshchikov
http://www.ls-software.com