At 08:48 AM 8/06/2007, you wrote:

>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)

You suppose right. Using a WHERE clause on a selectable SP is
horribly inefficient, since the whole set has to be generated in
order to search the completed output; and of course there is no
indexing available for searching that set.

>(b) the SP will only select the one record i am interested in (OK)

This is impossible, since the set does not exist in the
database. The engine has no way to target a single record since the
SP has to execute in order for the set to exist.

>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)

There are ways to engineer it (passing a list; using EXECUTE
STATEMENT; and others....) But the SSP really isn't the right way to
go about implementing ad hoc queries. You might get more
satisfaction from using a view (which may incorporate joins on other
views) which _does_ have access to real data.

./heLen