Single or each sql procedure for CRUD?

Question

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

Answer 1

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.

Answer 2

@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

Answer 3

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 ...

Answer 4

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.

Answer 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

Answer 6

@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 :)

Answer 7

it is better one for each, your database may be overloaded when you execute a single stored procedure that contains many SQL institutions

Answer 8

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

Answer 9

@ David Ok good I have not tried it about the store procedure generator with python..What about with php?

Answer 10

@david, thanks for your advice, im considering to search tools that help me to create those procedures.

Answer 11

@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

Answer 12

@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 😀

Answer 13

i get it,
thanks for your answer

Answer 14

@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 .

Answer 15

@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.

Answer 16

@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.