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!
Best Answer
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:
I still would not recommend this though.