This whole issue of whether to use SP’s or Querries did’nt really occure to me until my recent 70-229 Exam.(SQL Server Development). I did not have a reason to select one over the other since I did not know the benefits,pitfalls of both approachs. But Our Web Application ‘Client’ was totally done using SP’s and the ‘Admin’ was running on Transact SQL Querries.
Once I’ve done 70-229 I had every reason to believe that ‘Admin’ also better be shifted to Stored Procedures due to couple of reasons.

1. Our Application is totally Internet based which means that we have severe limitations when it comes
to guaranteeing a maximum application execution speed. (Network Bandwidth.)
This was a critical issue since even an ADSL connection is rare commodity in Sri-Lanka.

2. Also we have to think in terms of Optimizing Queries once the system is up and running and the Data Base is
fed with high volumes of data. This also could be better handled.(Maintenance cost will be less) with SPs

3. Later if the client wants to restrict certain user activities by introducing extra Views, Invoking/Revoking
permisins on SELECT, UPDATE, DELETE statements, these could be easily handled via Stored
procedures .

So Considering all these we thought that we have a very good case to Make our whole application run on SPs. To further justify my thinking I did some research and found some interesting articles which says exactly why SPs are a better choice over Transact SQL queries.

This I thought would be a good starting point for anyone who is going through the same dilema. This is also another article which explains the same stuff a little more in detail. Anyway google offers a lot on this topic.

This also speaks of taking certifications since this would not have stuck me so powerfully if no for 70-229.

Having said all this I’ll briefly jot down a few Advantages of using SPs Over Transact SQL Querries in your code.

* SP’s greatly reduces Network Traffic by limmiting the Network call to a simple RPC style call rather than
sending a whole Querry Statement from Client to SQL Server.

*Stored procedure execution plans are reused by SQL Serevr rather than parsing-optimizing and Compiling
Queries everytime as and when they are executing.

*Code reusability is high with SPs since your code is separate from your querries.

*Stored procedures do a better job in Encapsulating Logic. You can change and Manipulate SPs as long as the
parameters remain the same and returning result sets are intact.

*Finally Stored procedures helps you to implement a better security model with INVOKING/REVOKING user
rights.