>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

Don't use a single stored procedure with many different criteria possibilities. The performance can possibly be worse by a factor of 10 or more and you will have no control over it.

If you have the storage space, consider creating an indexed view for each possible join structure and query it dynamically from the business layer. This will simplify the execution plans (SQL Server will spend less time analyzing the SQL before running it).