• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1292
  • Last Modified:

Advantages and disadvantages of storage procedures

I have worked allot with SQL Server and I always used direct commands like sqlcommand, sqldatareader, etc, and never with storage procedures.

My question is: what the advantages and disadvantages of using then?

We have to create the storage procedures and the SQL instruction in the code, so why?

Any good example of it would be nice (VB).

2 Solutions
Aneesh RetnakaranDatabase AdministratorCommented:
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Quite frankly, server wise speaking I do not see any advantage of using down-in-nature SQL code as opposed to using stored procedures...There are only disadvantages to putting SQL in applicative API code: security, client side management of code TSQL, lack of readibility, difficult to troubleshoot, poor binding with server, poor caching (performance degradation in high concurrency)...

Hope this helps...
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Note that you can perfectly work and set up datareaders while using stored procedures.  Using stored procedures simply means you have a nice clean separation between the Data Layer and the Object Layer...
agreed with racimo
jpaulinoAuthor Commented:
Thanks guys.

I'm going in the right way!


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now