--- In firebird-support@yahoogroups.com, Pavel Menshchikov
<mpn2001@y...> wrote:

> 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

Thanks Pavel, after taking your advice (yes I eventually found your
earlier message, didn't show first time for some reason)
The stored procedure works a treat, so thank you once again for
removing this potential headache.
Steven