Apply Filters or Sorts in a Stored Procedure vs. in the Business Logic Layer
Sep
21
Written by:
9/21/2010 11:22 AM
A question came across StackOverflow yesterday asking whether it was better to perform sorting or filtering within a stored procedure or in the business logic layer of an application. This highly depends on the application and your needs. The general approach I use, and is based on advice given by those with a few more gray hairs than I have, indicates that filtering and sorting should be placed within the stored procedure logic.
The reason for placing this functionality within the stored procedure is due to the fact that SQL Server's database engine can better handle the load of performing these tasks than a web server (which is possibly hosting multiple sites with numerous users). Obviously, this practice doesn't fit for all cases such as those in which you need to allow the end user to do their own sorts or perform their own filtering. Providing end users with this functionality should be an exception, and not a rule.
Stored procedures are a necessity in protecting your data from malicious users. We've seen many examples on the web where a SQLDataSource contains a TSQL SELECT or UPDATE statement. These are great to review as learning concepts, but I do not feel that issuing TSQL from a web application is a secure practice. Instead, use of a stored procedure insulates, to some point, the data access layer from attack risks.
A question for my readers now: When developing a SQL Server Reporting Services report object, do you use a TSQL statement for your data source, or do you use a stored procedure or view?
Happy Coding!!