I would like to ask you, your opinion when it comes to implement a filtered search form. Let's imagine the following case:
1 Big table with lots of columns
It might be important to say that this SQL Server
You need to implement a form to search data in this table, and in this form you'll have several check boxes that allow you to costumize this search.
Now my question here is which one of the following should be the best way to implement the search?
Create a stored procedure with a query inside. This stored procedure will check if the parameters are given by the application and in the case they are not given a wildcard will be putted in the query.
Create a dynamic query, that is built accordingly to what is given by the application.
I am asking this because I know that SQL Server creates an execution plan when the stored procedure is created, in order to optimize its performance, however by creating a dynamic query inside of the stored procedure will we sacrifice the optimization gained by the execution plan?
Please tell me what would be the best approach in your oppinion.
