Dynamic SQL with stored procedures - pros and cons?

Question

I have read a lot of strong views (both for and against) of SP or DS.

I am writing a C ++ query mechanism (mySQL backend for now, although I can decide to go with C ++ ORM). I cannot decide whether to write SP or dynamically create SQL and send a request to the db mechanism. #

Any tips on how to solve?

Answer 1

Here is a simple answer:

If your programmers work with both a database and an encoding, keep SQL with the application. It is easier to maintain in this way. Otherwise, let the DB guys process it in SP.

Answer 2

You have more control over mechanisms outside the database. The biggest win in securing this outside the database is just maintenance (in my opinion). It would be a little difficult to manage the versions of SP compared to the code you create outside the database. Another thing to keep track of.

While we are discussing this topic, this is similar to handling data / schema migration. This is annoyingly complicated for version migration / schema processing, if you don't already have a mechanism for this, you will have one more thing that you will need to manage. It comes down to a simple management / version of these things outside of the database.

Consider a scenario in which you have an error in your SP. Now you need to change it, but then go to another developer database / sandbox. Which version is the sandbox and SP? Now you need to track multiple versions.

Answer 3

One of the main differences is whether you write “one true interface” or whether the center of your application is a database.

If you have several stored procedures at the front end, this makes a lot of sense because you reduce maintenance overhead. If you write only one interface, stored procedures are a pain because you lose a lot of flexibility when changing the data set, because you need to change the external interface, and now you need to perform code maintenance, version control, etc. In two places, Databases are a real pain to keep in sync with code repositories.

Finally, if you are encoding multiple databases (e.g., Oracle and SQL compatible code), I would completely avoid stored procedures.

You can, in some rare cases, after profiling, determine that some limited stored procedures are useful to you. This situation occurs less than people think it does.

Answer 4

The main scenarios when you MUST have a joint venture:

1) When you have a very complex set of queries with heavy compilation overhead and data drift, low enough that re-compilation is not required on a regular basis.

2) When the logic “Only True” is VERY complex to access a specific data set, it needs to be accessed from several different code bases on different platforms (therefore, writing several APIs in the code is much more expensive).

Any other scenario, it is controversial, and can be defined one way or another.

I also have to say that the arguments of the other posters about version control are actually not as big as my experience: having your SPs in version control is as simple as creating the "sql / db_name" directory structure and having a simple base "release database" "script that frees SP code from the versioning location to the database. Each company I worked with had some kind of setup, for example, a central one, managed by database administrators or an agency working as developers.

Answer 5

The only thing you want to avoid is to deploy your business logic at several levels of your application. The DDL and DML databases are sophisticated enough to maintain synchronization with the application code base as it is.

My recommendation is to create a good relational schema, but all your limitations and triggers are to keep the data consistent, even if someone goes to the database and tries to do something through some SQL command line.

Put all your business logic in an application or service that calls (static / dynamic) SQL, then wraps the business functions you are trying to open.

Stored procedures have two goals that I can think of.

• Helps simplify data access. A stored procedure does not have any business logic in it, it just knows about the data structure and provides an interface for isolating access to three tables and look only to get one piece of information.
• Comparing a domain model with data Model stored procedures can help to create a data model that looks like a given domain model.

After the program has been completed and profiled, there are often problems with the release to version 1.0. Stored procedures offer batch processing of SQL without the need for traffic movement between the DBMS and the Application. Speaking in rare and extreme cases due to performance, some business rules can be ported to the stored procedure side. Be sure to document exceptions to architectural philosophy in several well-known places.

Answer 6

Stored procedures are ideal for:

• Creation of multiple abstractions over complex queries;
• Application of certain types of attachments / updates for tables (if you also deny permissions for the table);
• Performing privileged operations that were usually not allowed to log in,
• Guaranteed an agreed implementation plan;
• Enhanced ORM features (batch updates, hierarchy queries, etc.)

Dynamic SQL is ideal for:

• Variable search arguments or output columns:
  • Additional search terms
  • Summary tables
  • IN with user-specified values
• ORM implementations (most of them can use SP, but cannot be completely built on them);
• DDL and administrative scripts.

They really solve different problems. Use what is more suitable for this task, and do not limit yourself to only one or the other. After you work on the database code for a while, you will begin to get a more intuitive feeling for these things; you will come across some kind of rat nest of strings for the query and think: "This should really go in the stored procedure."

Final note. Since this question involves a certain level of inexperience with SQL, I feel obligated to say, do not forget that you still need to parameterize your queries when writing dynamic SQL. Parameters are not just for stored procedures.

Answer 7

DS is more flexible. SP makes your system more manageable.