At 02:40 PM 6/02/2012, Net Newbie wrote:

>What is a good and recommended way to dynamically build a query inside
>stored procedure?
>
>For example a typical web portal search engine. user can select one or
>many input parameters for a search.
>For example car_model, fuel_type, color, gearbox_type etc. then into
>select statement where part should be put only those what have value.
>
>I started to look at first at "EXECUTE STATEMENT". but then in
>documentation found sentence "Operations will be slow because the
>embedded statement has to be prepared every time it is executed.".

Well, the same is true with dynamic SQL, if every query is completely ad hoc. If you can execute the same query many times with parameters, only the first execution will need to be prepared.

>then thought probably there is a better way? how to do it?

There doesn't seem to be a good reason to use a stored procedure for a search. Why not construct your query dynamically in your web app tier?

That said, you *can* construct a parameterised EXECUTE STATEMENT string in v.2.5. You might find it is as fast as using DSQL. You couldn't tell unless you tested both ways for the same use case with the same data. The main trap I see is that you'd likely be calling the SP for each and every search, given that the "dynamic" element of the task is user-selected search criteria.

./heLen