+ 7

Single or each sql procedure for CRUD?

when you creating crud, do you create single procedure for each action (insert, update, delete, select) or you create each one?

17th Nov 2017, 7:18 AM
Andika Romansyah
16 Answers
+ 11
That depends on your needs and perhaps on who will execute those procedures. If it is you and you know what you are doing, maybe you could allow yourself for a macro-like, does-it-all prcedure. But for other users, especially if they are using some kind of GUI, it is better to have single-task procedures. After all, if something breaks, it is easier to fix this one without affecting other tasks.
17th Nov 2017, 7:25 AM
Kuba Siekierzyński
Kuba Siekierzyński - avatar
+ 6
@monsieur, what im working on "event organizer" project, there will be 3 type of user. 1. root admin, manage all of it 2. event admin, manage their own event 3. user, people who needs services from event owner im working at root admin first, so im planning to create procedure like this one create procedure someTable(action, param1,param2) begin case action=insert then insert qyery case action=update then update query and so on end
18th Nov 2017, 12:45 AM
Andika Romansyah
+ 5
Well, as a good practice in development and audit you should have single-task procedures to make sure of the task you are executing and saving "the journal" in a log file for tracing the actions made ...
17th Nov 2017, 1:39 PM
Haddada Zakaria
Haddada Zakaria - avatar
+ 5
it's a good practice to separate each task in different procedures. each one with its own target. that give you a better overview on your code. your code is also easier to maintain and to extend.
17th Nov 2017, 10:11 PM
David RIEHL
David RIEHL - avatar
+ 5
it depends either each procedure for each dml or one for one procedure for all dml .it depends of the context you are working .Let's us know the context you are working on so that I can tell more
17th Nov 2017, 10:31 PM
MONSIEUR GNETO
MONSIEUR GNETO - avatar
+ 5
@Andika, I agree with @David Carroll, he was right, besides, the procedure will grow bigger and it will be harder for you to maintain or enhance later. I suggest follow @David Carroll suggestion, it's better for the future :) #savetianglistrik :)
19th Nov 2017, 9:58 AM
Ipang
+ 4
it is better one for each, your database may be overloaded when you execute a single stored procedure that contains many SQL institutions
20th Nov 2017, 2:08 AM
Juan Esteban Solis Duarte
Juan Esteban Solis Duarte - avatar
+ 4
An option to consider for making this easier to maintain is to use a script or code template tool to autogenerate the CRUD stored procedures. MS SQL Server has a command line tool for this and GUI tools in SQL Server Management Studio and Visual Studio .NET IDE. - http://www.sqlbook.com/sql-server/sql-script-to-automatically-generate-crud-stored-procedures/ - https://weblogs.asp.net/stevewellens/automatically-generate-stored-procedures-with-visual-studio - https://solutioncenter.apexsql.com/how-to-create-and-use-crud-stored-procedures-in-sql-server/ - https://www.apexsql.com/sql_tools_complete.aspx (Free VS.NET plugin with cool template management.) - https://knowledgebase.apexsql.com/creating-crud-procedures/?utm_source=sqlshack&utm_campaign=complete&utm_medium=native_link&utm_content=creating-using-crud-stored-procedures I did a quick search for options to generate CRUD procs on MySQL and found this nice little Python script: - https://github.com/jongha/spgen NOTE: I've not used this before. However, it main py file seems to be straight forward with the CREATE PROCEDURE scripts for each table specified for the database connection. - https://github.com/jongha/spgen/blob/master/spgen/spgen.py
20th Nov 2017, 4:32 AM
David Carroll
David Carroll - avatar
+ 4
@ David Ok good I have not tried it about the store procedure generator with python..What about with php?
20th Nov 2017, 5:42 AM
MONSIEUR GNETO
MONSIEUR GNETO - avatar
+ 4
@david, thanks for your advice, im considering to search tools that help me to create those procedures.
20th Nov 2017, 6:12 AM
Andika Romansyah
+ 4
@David I have tried to implement an ORM using PDO OBJECT WITH PHP LANGUAGE .checkout with this link below https://code.sololearn.com/wmI8Amv59OYp/#php
20th Nov 2017, 8:11 PM
MONSIEUR GNETO
MONSIEUR GNETO - avatar
+ 3
@monsieur, can you link to me any crud example project using oop php please. i never use any oop on my project, just simple function with no framework. and i'm interesting to learn it 😀
21st Nov 2017, 1:04 AM
Andika Romansyah
+ 2
i get it, thanks for your answer
17th Nov 2017, 8:35 AM
Andika Romansyah
+ 2
@Adinka ok try that If you respected the syntax. I think is going to work...It means that every time user want play with data the request type will be needed .
18th Nov 2017, 4:50 PM
MONSIEUR GNETO
MONSIEUR GNETO - avatar
+ 2
@Andika... I would strongly advise against creating a single entry CRUD procedure as you described in your previous post. There are so many reasons why this would be problematic from design, performance, and good practices perspectives. If consolidation is what you are looking to achieve, then eliminate the use of stored procedures for general CRUD purposes and utilize an ORM like hibernate, Entity Framework, or many of the various options depending on the platform you are working on. I can probably write pages of reasons why attempting to consolidate this type of procedure in the database should be avoided. However, I'll try to stick to only a few bullet points that imediately come to mind: 1. Each CRUD statement uses different parameters and returns different results. Managing this in a single proc will require adding business logic to your database. Examples: - Insert statements use parameters for all fields except for autogenerated primary key (PK) value. The result will typically be the PK created for the record. - Select statements typically take minimal parameters needed to filter on the result set being requested. The result will be the recordset. - Update statement parameters include the same parameters as inserts, but also include the PK to specify which record to update. - Delete statements typically use the PK as the parameter. The results for Update and Delete statements are usually some value indicating success or number or records affected. 2. Atomic transactions will be a nightmare to implement with this approach. If you are not aware of this concept, then skip this reason for now. 3. DB level security and control is no longer possible at the command level. 4. Monolithic monster size procedures will begin to form to support various query scenarios. I'm only scratching the surface here. Please, do yourself a favor and don't take this approach. You will regret it later. Spare yourself the embarrassment of ever having to explain this design to others.
19th Nov 2017, 5:16 AM
David Carroll
David Carroll - avatar
+ 2
@Monsieur Gneto I didn't see a similar script for this in PHP. I'm sure you could write something if you really wanted to. You could even port the Python scripts to PHP. That said, it would be somewhat awkward to write this in PHP because you would need to go through a web browser to initiate the script via a local web server running PHP. It's not impossible, just an odd development workflow to follow. With Python, you can initiate the script from a command line console, which would be more common for a development workflow. Anyway, good luck and share what you discover if you pursue this further.
20th Nov 2017, 8:25 AM
David Carroll
David Carroll - avatar