Post reply

Single SQL Stored Procedure for Select/Insert/Update/delete

  • June 8, 2010 at 10:37 am

    #219753

    Hi,

    I want to know if I can and if its a good practice to use a single stored procedure to perform all the operations i.e select/insert/update/delete.

    For instance, if I have a Customers table can i create a single stored procedure

    dbo. sp_customers(ID, FirstName, LastName, Address, command)

    In this I'll pass command as 0-select, 1-insert, 2-update, 3-delete.

    In the stored procedure I'll check if(@command = 1) select stament.

    I'll make FirstName, LastName, Address as optional so that according to the need only we need them. e.g in case of delete we only need to pass ID and command.

    Please let me know its urgent. I know it can be done but I want to know if its a good practise and if now why.

    Thanks in advance

    Pooja

  • June 8, 2010 at 10:45 am

    #1177567

    Pooja

    Why is this urgent? Is it a homework assignment?

    I wouldn't recommend doing this since it is likely that an inappropriate execution plan will be cached if you use the stored procedure for different operations one after the other. This could affect performance.

    John

  • June 8, 2010 at 10:54 am

    #1177576

    Hi,

    Thanks for the reply.

    Its urgent because I need to deliver something soon based on this.

    I was asking this because if I have 10 tables and every table I need to have select, insert, uodate delete, it will be 40 SP's and will be difficult to maintain.

    -Pooja

  • June 8, 2010 at 10:59 am

    #1177581

    Pooja

    Thanks for the explanation. Bear in mind that we're not paid to answer these questions and most of us also have day jobs, so it isn't always possible to provide you with an answer urgently.

    40 stored procedures isn't difficult to maintain, especially when you compare it to the performance mess that may be the alternative. As with everything, test it thoroughly before you put it live.

    John

  • June 8, 2010 at 3:28 pm

    #1177702

    John Mitchell-245523 (6/8/2010)

    Pooja

    Thanks for the explanation. Bear in mind that we're not paid to answer these questions and most of us also have day jobs, so it isn't always possible to provide you with an answer urgently.

    40 stored procedures isn't difficult to maintain, especially when you compare it to the performance mess that may be the alternative. As with everything, test it thoroughly before you put it live.

    John

    I agree with John. This procedures is not a big deal. I know someone who tried what you want to try. His stored procs became harder to maintain because they became too involved. I believe my friend accidently changed something so that when he did a select he fell into the update section of his procedure. A big opps.

    My suggestion is to write one of the procedures so that it has a select, an insert, an update and a delete. You can then see for yourself that breaking them into separate procedures is a good idea.

    I hope this helped.

  • June 8, 2010 at 3:43 pm

    #1177707

    Pooja,

    You haven't explained why you want to do this or why this is so urgent to you.

    If what you want is simply to perform the standard functions on ten tables you are probably as well off not using stored procedures at all but just using the raw statements.

    The optimiser will use parameter sniffing on the raw statements and you don't need any SP's.

    If you really have to use SP's, then I agree with the other respondents, write 40 with a single statement in each.

    Tim

    .

  • June 8, 2010 at 4:07 pm

    #1177718

    Hi,

    I also agree with the other writers. Use raw SQL statements or create 40 procedures.

    If you decide to create the procedures have a look at the ssms toolpack => here

    One feature is CRUD (Create, Read, Update, Delete) stored procedure generation...

    -----------------------
    SQL Server Database Copy Tool at Codeplex

  • June 9, 2010 at 8:25 am

    #1177987

    2500 stored procedures is a little difficult to maintain. 40 is nothing.

    No, I wouldn't recommend jamming all the code into a single procedure. In fact, I'd recommend, as much as you can, purpose building your procedures. Don't create a single read procedure for the table, create as many as you need. Especially on reads you need to consider that most of the time you're not simply going to be pulling from a single table, in the way that you write to them. You're going to be supplying queries with JOINs between more than one table. So you'll want a procedure for each distinct set of data that you need to return to your app.

    As far as the CUD part of CRUD, you can look at using the MERGE statement to provide a single path for some of your data manipulation, but it's not going to work in every case either.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • June 9, 2010 at 8:51 am

    #1178011

    I'm going along with what the other posters are saying, with a twist..

    Grant makes a great point about how the data is accessed, usually not from a single table.

    My twist is that I don't usually have a create AND an update sproc, I usually bundle them and make the sproc smart enough to decide which is appropriate. Not always but usually.

    In most cases I don't allow deletes so I don't have a sproc for that, so that leaves one sproc for writing and 1-to-many sprocs for reading depending on how I want the data.

    So in this case I'd end up with 10-20+ sprocs, a VERY manageable number, not like the 600 I had in another project.

    CEWII

  • June 9, 2010 at 3:24 pm

    #1178317

    Thanks everyone !!!

    I implemented all the stored procedures separtely.

    Thanks

    Pooja

  • June 10, 2010 at 6:49 am

    #1178566

    I'd advise against using sp_<procname> naming

    It causes a performance overhead - and potentially unexpected outcomes. With sp_ proc names, the engine will first look in Master, then if it's not found in master it will look in the current session db (thus incurring a performance overhead). If you have a sp_ proc with the same name as one of the system stored (or other) procedures in master, this will be executed instead of the proc with the same name in your session database

  • June 10, 2010 at 8:09 am

    #1178620

    What was only mentioned in passing was the performance mess that this would cause. The reason for this is that each stored procedure creates an execution plan based on what it's doing. An execution plan for each of these actions will most likely be different.

    If you add the RECOMPILE hint, this will also cause a performance problem from the procs being constantly recompiled.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply