Subject Re: stored procedure syntax and performance
Author martinthrelly

thanks very much for your considered reply. as a result of this thread
i have decided to use SPROCs for insert/update/delete statements. for
the select statements i am going to either use (a) views (b) standard
sql or (c) SPROCS utilising input parameters. (d) a bunch of different
sprocs e.g. SP_USER_S_ID, SP_USER_S_CLIENTID, etc.

the SPROC input parameter solution would mean having input parameters
and dynamic WHERE clauses like this:

where (:in_id is null or id = :in_id)
and (:in_clientid is null or id_client = :in_clientid)
and (:in_username is null or u.username = username)

i suspect this would yield a faster performance than using a view?
nevertheless i am still going to use views for the sake of simplicity.

select statements by nature need to be flexible. i.e. if i have a
table then it is very conceivable that i may want to perform selects
on a variety of different criteria (PK, FK, etc. so i am wondering
where SPROCS fit into this with Firebird 1.5? are they inadequate for
the job? i am just trying to understand better. thanks.