Hi,

> I have a stored procedure that I wish to pass in a variable (varchar(200))
> that contains the name of a field to use in a query, along with the search
> value to search (varchar(200) also).
>
> In trying to construct the query in the stored procedure, my WHERE clause
> looks like this:
>
> WHERE (ASSET.RECORD_INACTIVE <> 'Y') and (:V_SEARCH_FIELD LIKE
> ASCII_CHAR(39) || :A_FOR || ASCII_CHAR(39))

:V_SEARCH is a value, not a column.

> My goal is to have the query read:
>
> WHERE (ASSET.RECORD_INACTIVE <> 'Y') AND (ASSET_SERIAL_NO LIKE 'ABC123')
>
> I've tried this query out, resolved as above and it works fine. But when

I

> attempt to execute it through the stored procedure, it always returns no
> values.
>
> Do I have the syntax for this type of thing wrong? If so, how should it
> look?

See above. What you are getting is (for example):

AND ('ASSET_SERIAL_NO' LIKE '''ABC123''')

If you want to address different columns, don't use variables. You should
use
dynamic SQL (see EXECUTE STATEMENT in the Firebird 1.5 Release
Notes).

The ASCII_CHAR(39) will concatenate a ' to your string value in the
parameter, it will NOT enclose the value in quotes like you want to.

Parameters aren't "search and replace" markers, they're places to bind
a value to.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com