Hello Forum,
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 created already 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 great example online yet of conditional logic used in a stored procedure.
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 have attached my data model for reference. Thanks in advance for your help!
You can write a conditional code like shown below:
IF (@action = 'INSERT')
BEGIN
-- your insert statement here
RETURN
END
IF (@action = 'DELETE')
BEGIN
-- your delete statement here
RETURN
END
You could use ELSE keyword as well to check one condition after another, in which case, you won't need the return statements.
However, looking at your tables, a better approach perhaps might be to write stored procedures for specific actions - for example, inserting/updating inventory, or orders, or stores and so on. Expose the actions that make sense to the user from a business perspective. They may not care how you are storing it in the database.
We write one Sproc for each of those "CRUD" (Create, Read, Update, Delete) functions - except that we combine Insert and Update (with a parameter to indicate Insert (must not exist), Update (must exist), or UpSert (don't care, Insert if not existing, Update otherwise)
(We also have "Find" Sprocs which tend to have complex logic to allow the user to select "anything they want" and include paging logic, lookups to associated data and so on.
The parameters for our Sprocs are radically different:
GET : Primary Key fields only - SELECT Col1, Col2, ... WHERE PKey1 = @PKey1 AND PKey2 = @PKey2 ...
INSERT / UPDATE : One parameter for each column in the table, plus a @Mode parameter to indicate Insert, Update, UpSert
DELETE : Primary Key fields only
We mechanically generate all our CRUD SProcs. 99% of them are unchanged by human hands from the mechanically generated code, so apart from polluting the Name Space with 4x the number of "names" there is no benefit.
Benefits of separate Sprocs:
Discrete code. SQL won't be caching the Sproc query plan based on an Update when it comes to do a Delete. Insert/Update will have Transactions and probably a load of code to validate the parameters, and perhaps logic to Archive / Audit / Log the data too; perhaps also to "Pass" it to some 3rd party database / process / APP. IME its a huge amount of baggage, compared to either GET or DELETE.
Relevant Parameter Lists - only the relevant parameters included. That probably means better performance - SQL won't be juggling all the [unused] Insert/Update parameters for a Get
Better Revision Control - ability to see whether Get, UpSert or Delete logic [explicitly] has changed, rather than just being able to see that "Something" has changed.
Less code manipulation if one function changes - e.g. you decide to change the logic of your Delete process and want to roll that out to one/many/all! Delete Sprocs
and include paging logic, lookups to associated data and so on.