General information
Forum:
Microsoft SQL Server
Title:
Which scenario would you choose?
Environment versions
SQL Server:
SQL Server 2000
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only