Advantages and disadvantages of storage procedures

Posted on 2007-09-28
Last Modified: 2013-11-26
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).

Question by:jpaulino
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    LVL 23

    Accepted Solution

    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...
    LVL 23

    Expert Comment

    by:Racim BOUDJAKDJI
    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...
    LVL 8

    Expert Comment

    agreed with racimo
    LVL 48

    Author Comment

    Thanks guys.

    I'm going in the right way!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how the fundamental information of how to create a table.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now