While designing DAL layer we normally stuck with one debate, Stored Procedures or Embedded SQL Query? I also faced the same problem. I searched on the internet, read some books and discussed with some friends. I am trying to compile all these discussion in single post.
Stored procedures require only use the SqlCommand object to execute. Other way is to deal with SQL is to store your SQL statements as text (Embedded SQL Query) to be executed with SqlCommand objects. Different people will give you different answers and opinions, and both of these options have their pros and cons.
Some sources saying, stored procedures provide better performance than SQL text queries because they are "pre-compiled" and cached in-memory by SQL Server. SQL text queries as well, as long as you use parameterized SQL statements. Therefore, the performance of stored procedures and SQL text queries is similar in most cases. However, the name of a stored procedure is shorter and invariant compared to a SQL statement, so it may be easier for the SQL Server engine to find the cached execution plan, but this shouldn't make a significant difference in most cases.
Security is the areas where stored procedures have edge over embedded SQL queries because you can give your web SQL user account the permission to execute only certain procedures, while not giving full control over the entire underlying tables. Without stored procedures, you can choose to give a user the INSERT permission (for example) on one table but not on another table, and you can limit access to some table fields by creating a view that doesn't include them. Still, stored procedures are better than this because they enable you to add row-level security: If the user account doesn't have any access to the underlying table but only to a stored procedure that only retrieves rows matching a specific filter, then there's no way the user can retrieve/work with the other rows. However, row-level security is admittedly a pretty rare requirement.
Another advantage of stored procedures is that they provide a further layering of code. There's no SQL code in the DAL layer. This is often desirable because it allows us to deploy the compiled DAL, and later adjust something in the stored procedures' SQL code without any impact to the DAL; we don't have to recompile or redistribute the DAL.
Stored procedures usually include a batch of statements, not just one. If we had to execute them with SQL text queries, we would have to execute multiple separate commands, which would mean much more network traffic for sending data back and forth. When we call a stored procedure, the network traffic is minimized because you call a single procedure with a short name, not dozens of SQL statements, each of which may be hundreds of characters long.
Stored procedures are not always the best solution. The biggest advantage to using SQL text queries with SqlCommands, instead of stored procedures, is that they are more flexible. Sometimes we want to implement advanced search on behalf of forms in the UI, whereby the user can partially fill in some data in text boxes to filter the content against some database fields and order the results by other fields. Often, the results must also be paginable. If we have many rows in your results, we don't want to kill performance and scalability, or bore the user by making her wait a long time to download hundreds of records. The query to support all these features would be different according to the fields the user filled in and the sorting option she chose in the UI. If you had to build the query from inside a stored procedure, you would need to write a lot of IF...ELSE statements, which could become unmanageable if you have a lot of optional fields. By using a dynamic type of query in which SQL text is generated at runtime based on UI selections, and then executed in a SqlCommand as SQL text, the overall code is far more elegant and maintainable than any attempt to do this with stored procedures would be.
One big reason about using stored procedures instead of SQL text is that they make your code tightly bound to the SQL Server RDBMS, so that if you ever need to support another data store you would need to rewrite most of the SQL stored procedure code. This would be needed because various RDBMSs have a much different stored procedure language, even though their SQL dialect is usually pretty close to the standard ANSI SQL specifications as there are no standards for stored procedure languages ).
Reference : ASP.NET 2.0 Website Programming: Problem - Design - Solution
Cheers!!!
Mahesh
maheshsingh21@hotmail.com