Hi Sylvain,

A fairly standard approach to this would be to create multiple stored procedures for each filter scenario you require. Whilst this may seem like a lot of stored procedures, its not really. We have hundreds of stored procedures on our system and they really are our preferred way to access data from not only VFP but from .Net too.

To make life easier, you can quite easily source stored procedure code generators that you just point at the database and they will generate a full set of CRUD procedures that you can use as-is or modify to your liking. Also, studying generator ouput code is a good way to learn how how to write this type of stored procedure.

HTH



>Given that the user can select which fields are displayed and what filter to use, what is the prefered way of getting the result?
>
>1) Build dynamically the SELECT-SQL in the business layer and pass the request to SQL Server
>Pros:
>- I get exactly what I want in one call
>Cons:
>- SQL Server must recompile the request each time
>- Security concern
>
>2) I use a parametized stored procedure to return the result
>Pros:
>- Compiled once
>- Secure
>Cons:
>- Can have over 20 filters, the code will be ugly and may impact performance
>
>3) Use a store procedure to return all results and filter the result in the business layer
>Pros:
>- Compiled once
>- Secure
>- The stored procedure will be cleaner than option 2
>Cons:
>- Must filter the result set
>- Performance consideration
>
>4) Other?
>
>I don't have much experience with SQL Server, so I will appreciate every suggestions you may have.
>
>TIA