August 10, 2015 at 2:54 pm
INSERT / UPDATE operations can be consolidated gracefully into a single MERGE statement, and the input parameter, perhaps an XML datatype, would be the same. So that could be one stored procedure. However, DELETE operations are a different case usage entirely and should generally be a separate stored procedure.
From what I've seen in the past, these situations where there is a need to code up 100+ CRUD operations; just let the application developers INSERT/UPDATE/DELETE the reference and meta-data tables directly and reserve stored procedures calls for more complex transactional operations that effect multiple related tables. You can mitigate the possibility of data corruption by implementing unique keys, foreign keys, and other constraints.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 10, 2015 at 3:05 pm
GilaMonster (8/10/2015)
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/deletehttp://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Agreed. Just because you can doesn't mean you should.
My first question to anyone that handed over to me stored procedures like that might be: "Is your resume up to date?"
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 11, 2015 at 7:05 am
Alvin Ramard (8/10/2015)
GilaMonster (8/10/2015)
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/deletehttp://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Agreed. Just because you can doesn't mean you should.
My first question to anyone that handed over to me stored procedures like that might be: "Is your resume up to date?"
Their resume probably is up to date. Most of the schmucks who write crappy stored procedures don't get fired, they just get tired of maintaining their own code until they walk out to door... so they write crappy code somewhere else. Then folks like us are left to clean up the mess.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 11, 2015 at 3:10 pm
TheSQLGuru (8/9/2015)
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.
Kevin, thanks for the reference to SSMS Tools Pack, I'd not heard of it before. I'm checking it out now, looks pretty cool for $30~.
To simplify things, it can be found at http://www.ssmstoolspack.com/.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 12, 2015 at 6:43 am
TheSQLGuru (8/9/2015)
...There are many other CRUD generators out there too, including free options. SSMS can do it even, albeit one at a time.
Where is this feature in SSMS?
It would be nice to right click on table and then choose: Script Table as... CRUD.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 12, 2015 at 1:36 pm
Eric M Russell (8/12/2015)
TheSQLGuru (8/9/2015)
...There are many other CRUD generators out there too, including free options. SSMS can do it even, albeit one at a time.
Where is this feature in SSMS?
It would be nice to right click on table and then choose: Script Table as... CRUD.
Find a table in SSMS Object Browser, right-click, Script Table as, pick INSERT/UPDATE/DELETE/SELECT ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 12, 2015 at 3:01 pm
TheSQLGuru (8/12/2015)
Eric M Russell (8/12/2015)
TheSQLGuru (8/9/2015)
...There are many other CRUD generators out there too, including free options. SSMS can do it even, albeit one at a time.
Where is this feature in SSMS?
It would be nice to right click on table and then choose: Script Table as... CRUD.
Find a table in SSMS Object Browser, right-click, Script Table as, pick INSERT/UPDATE/DELETE/SELECT ...
OK, I that helps a lot, and I use it all the time, but it's not coding up the framework for an actual CRUD stored procedure complete with header, parameters, error handling, etc.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply