August 9, 2015 at 2:02 pm
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 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.
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!
Hello Forum,
Grey Kitten
August 9, 2015 at 5:28 pm
It's just my opinion, but why would you want one stored procedure per table? The problem this will cause down the road is if you want to deny one of the read/write/update/delete permissions to an object. If you leave the stored procedures separate, you can assign permissions to each one, and assigning/updating permissions is really simple.
August 9, 2015 at 5:38 pm
The reason why is because I would have to write 28 stored procedures which is much more time intensive and involving than I would like. I was hoping I could enter a parameter like @insert, @select, etc, to activate the procedure to perform that action and ignore the other 3. But I've never done that and really don't know how that would look, let alone the conditional logic required. Performance is not an issue because there aren't that many records yet per table. The goal is not performance; it's saving time and gaining convenience on a small scale example.
Thanks!
GK
August 9, 2015 at 5:48 pm
If you lump all the CRUD into one stored procedure, how do you control who can do what? If the objects are separate, you can add them to the proper schema and you're home free. With all the CRUD in one stored procedure, you can't do that.
August 9, 2015 at 5:55 pm
Once I've created user roles for the employees and managers, I'll be able to limit what each can do. I haven't had a chance to do that yet. Make sense?
August 9, 2015 at 6:18 pm
The reason I prefer them as separate stored procedures is that I can grant rights to the SELECTS to one group, the INSERTS to another, etc. If you lump them together in one big stored procedure, it means everyone with access to that stored procedure can do anything in the table, and that's going to be a nightmare to manage.
August 9, 2015 at 7:07 pm
This is what the client has requested. It would be greatly appreciated if you could help.
August 9, 2015 at 8:42 pm
August 9, 2015 at 8:48 pm
Do NOT do what you want to do!! Here is a Guruism: slapping together code quickly is BAD, often for multiple reasons. Plus you will be creating a maintenance/troubleshooting nightmare.
Get SSMS Tools Pack. Inexpensive add-in for SSMS and it can build all your CRUD for you and LOTS more useful stuff. There are many other CRUD generators out there too, including free options. SSMS can do it even, albeit one at a time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 9, 2015 at 9:05 pm
That's basically what I was trying to point out. I would explain to the person requesting this the kinds of issues this kind of design will cause. Like no separation of rights (insert only/read only/update only...)
August 10, 2015 at 5:11 am
You're going to spend a lot more time trying to create this magic query for each table that will dynamically allow INSERT/UPDATE/DELETE/SELECT than it will take you to just create the procedures you need. This is especially true of your standard INSERT and DELETE queries. Just do the necessary work. You'll be so much better off for it.
"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
August 10, 2015 at 6:05 am
You can do it, but it is not a good idea. It's liable to cause you problems down the line. Just write one proc for insert, one for update, one for delete, or use an ORM for the basic insert/update/delete
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2015 at 8:24 am
Just curious, but what does "google-fu" mean in your response?
August 10, 2015 at 8:27 am
redmittens7 (8/10/2015)
Just curious, but what does "google-fu" mean in your response?
It means learn how to use online search tools such as Google, Bing, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 10, 2015 at 1:25 pm
I typed in many variations of specifically what I was trying to do. But thanks for the great advice.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply