Creating one stored procedure for all CRUD operations

Question

I'm very inexperienced at writing stored procedures with conditional logic and I could really use some help. I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table. I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure. You'd think there would be more examples online but still struggling to find a good one.

Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?

I recently found this post on this website: (Separate stored procedures for inserts and updates?) but I don't know how it would be modified to also include the other two operations for selecting data and deleting data.

I have attached my data model for reference. Thanks in advance for your help!

Answer 1

I would strongly advise against this route. While it seems advantageous to only have 7 stored procedures rather than 28, it will turn into a troubleshooting, tuning, and maintenance nightmare.

I don't know what dbms you are using, but the advice should apply to all of them.

From a troubleshooting and usage analysis standpoint you will have a hard time. Most reporting you get from your dmvs doesn't show the parameters the user submitted to execute the stored procedure. You are going to have to capture them yourself or run traces to see it in real time. This makes it incredibly difficult to determine how these stored procedures are being used, and how the users are using the system. Are they reading from the db more than inserting or deleting? Without digging deep you won't know. If you break them out into different stored procedures you can see which ones are used when and how often.
From a maintainability standpoint you would think that less files would be better, but it actually makes it worse. Now, whenever you want to change something to the insert portion of a stored procedure you are touching the part that inserts, updates and deletes, which means that they are all added to the regression testing scope. It also means that when you deploy new stored procedure code you'll impact people that are utilizing that table no matter what they are doing. From a tuning standpoint you are actually working against most dbms query optimizers. The optimizers will look at the data in the tables needed and try to determine the best way to retrieve data from them. The issue here is that the operation is not predictiable because your operation relies on parameter sniffing. Also, the strategy of 1 stored procedure to rule the table makes a few assumptions. Mainly that the way that you select, insert, update, and delete data will always be the same. What happens when that changes? You won't want to keep adding because it won't be easy to navigate. It's also hard to determine which indexes to make being that it's extremely difficult to determine how the optimizer is going to react.

From a usability standpoint, it makes things difficult. When you insert you'll need every column passed in as a parameter, but when you select it will only be the id you are filtering on, when you update it may only be the column you want to update and you have to make the query understand this. This normally leads to dynamic sql which is its own nightmare.

The long and short of it is that stored procedures are really good for doing very specific things. The more specific the better most of the time. While this doesn't lead to code reusability and a lot more stored procedures to work with, it makes things a lot easier to troubleshoot, maintain and tune along your applications growth. It is also more intuitive to developers that need to use the database.

If you do want to go this route, the only real way to do it would be if statements trying to sniff our what a user wanted to do based on parameter:

IF @action = 'insert' 
 INSERT INTO......

I still would not recommend this though.

Answer 2

I would recommend against this approach. While it may seem easier to do something like this it will, in the end, become a performance and maintenance nightmare for you. The time it will take you to write 7 procedures that do everything will be the same as if you just go ahead and create the 4 procs per table, believe me, I've tried it your way myself long ago and found that it did not make anything easier.

Answer 3

As you are saying that a single data model is goanna undergo Insertion,Update,Deletion,Select and you have 7 data models and you are thinking that writing 4 stored procedure for single data model which results in (7*4=28) which was not a generic one.

I agree with you as a developer perspective as every developer wants their code to be more generic,less in number of lines,reusable code but it fails to fulfil maintainability and easy to debug conditions.

Consider i have built the stored procedure in your way with the help of logical if operator. And am making a select operation so am passing select as one of the parameters and i need all the columns as result set . so i will be making all the columns as a result . Then if i want to make an update may be i dont need any result set from server or may be a bool value indicating success or failure alone enough. Similarly it vary for Insertion,deletion.

Your assumption fails in those cases

Answer 4

Maybe this query will help you to get CRUD operations in one procedure:

Table 1: Customer

create table customer 
(
    id int not null,
    name nvarchar(50),
    phone nvarchar(20)
)

Table 2: CustomerAddress

Create table CustomerAddress
(
    cusid int,
    city nvarchar(50),
    State nvarchar(50),
    Pin Nvarchar(10)
)

Stored procedure:

 create Procedure uspCustomer
        @action nvarchar(15),
        @Cus_ID int,
        @Cus_NAME nvarchar(500) = NULL, 
        @Cus_NO nvarchar(50) =NULL,
        @CITY NVARCHAR(100)=NULL,
        @STATE NVARCHAR(100)=NULL,
        @PIN NVARCHAR(100)=NULL
as
    IF(@action = 'Insert')
        BEGIN TRY
        BEGIN TRAN
            INSERT INTO Customer (id, name, phone)
            VALUES (@Cus_ID, @Cus_NAME, @Cus_NO)

            INSERT INTO CustomerAddress (cusid, city, State, Pin) 
            VALUES (@Cus_ID, @CITY, @STATE, @PIN)

            COMMIT
        END TRY
        BEGIN CATCH
         IF @@TRANCOUNT >0
         ROLLBACK
        END CATCH
        IF(@action = 'Update')
        BEGIN TRY
        BEGIN TRAN
        UPDATE Customer SET name=@Cus_NAME,phone=@Cus_NO WHERE id = @Cus_ID
        UPDATE CustomerAddress SET city=@CITY,State=@STATE,Pin=@PIN WHERE cusid = @Cus_ID
        COMMIT
        END TRY
        BEGIN CATCH
         IF @@TRANCOUNT >0
         ROLLBACK
        END CATCH
        IF(@action = 'Delete')
        BEGIN TRY
        BEGIN TRAN
        Delete from customer WHERE id = @Cus_ID
        Delete from CustomerAddress WHERE cusid = @Cus_ID
        COMMIT
        END TRY
        BEGIN CATCH
         IF @@TRANCOUNT >0
         ROLLBACK
        END CATCH
        IF(@action = 'Select')
        Begin
        select cus.id,cus.name,cus.phone,cusadd.city,cusadd.State,cusadd.Pin from customer cus inner join CustomerAddress cusadd on cus.id=cusadd.cusid  
        end

Note: passing parameter value as null in stored procedure handles the situation for select with only one parameter that is id.