Subject stored procedure syntax and performance
Author martinthrelly
i am calling my stored procedure to select a single record with .NET
syntax like this.

"select * from [SP_NAME] where id = ?";

the where clause changes depending upon whether my DAL is grabbing the
record by a primary key or foreign key.

i am concerned that my current method could be inefficient. with the
above query, what will happen?

(a) the SP will first of all do its default record selection of all
records. afterwards it will apply the where clause? (inefficient)
(b) the SP will only select the one record i am interested in (OK)

i am thinking it is (a) in which case is the best fix to use input
parameters instead? this is ok but i lose the flexibility as i will
need lots of input params depending upon what the user is selecting
by. (if you follow my drift)