Best practices for creating and reviewing procedures

Question

• What is the best way to create, change and evaluate procedures?

Where work we do of the following flow:

• We check if the trial exists if we delete it and then create again.

To Verse I use a header similar to this:

/*------------------------------------------------------------------------------
Nome Procedure: SP_NOME_PROCEDURE
Projeto.......: Nome Projeto
--------------------------------------------------------------------------------
Autor                           Data                            Descrição
-----------------------------   ----------                      ----------------
Desenvolvedor 1                 01/01/2014                      Criação
Desenvolvedor 2                 01/02/2014                      Alteração
------------------------------------------------------------------------------*/

Answer 1

TL;DR

In my experience, the best way to ensure proper updating of a system as a whole is by versioning all artifacts, including those from the database, into a version management system (SCM), and ensuring that all updates will be applied.

Malpractice

Speaking specifically of the database, I’ve seen several teams that used a database in development as a model and then exported the changes to production. This has always resulted in many problems, such as: someone may delete the database unintentionally, someone may make changes without others noticing it, someone may overwrite some process by mistake and no one will notice and so on.

Another approach I consider flawed is for developers to create a specific script for each version of the system. For example, they share a folder on the network and with each change someone goes there and adds what they did. The problem is someone always forgets something. And it always happens that someone wants to update N versions of a base, forcing developers to create a custom script together with the changes of these N versions.

Good practice

Versioning in a version management system (SCM)

So I have best practice all the database elements (user-defined types, tables, table changes, views, procedures, triggers and data load) each in its own file .sql in a versioned folder in the SCM.

Database artifacts always receive the same tag source code to keep everything from system "married".

Idempotency

If an error occurs in the middle of a script the database structure can be partially updated. Then you will have to individually check which updates have been applied or not applied and build a new script based on this. It’s a big job.

But if each change checks that it has already been applied it does not cause errors if it is executed again.

So every file .sql individual should check in some way whether it has already been executed before attempting to apply changes. For example, a column addition checks if the column already exists.

In the case of procedures, triggers and views, they can always be recreated, exactly as quoted in the question.

Concatenation into a single script

You can also use any tool to concatenate the various files into one or more large files to be executed in the target environment.

Since each file is idempotent, merging them all into a single file ensures that the system version will always be the most current, even if the script is executed on a very outdated basis.

In a project I worked on, where we had all the artifacts from the versioned and idempotented database, we set up a script that was able to update the base from one version to another as well as create the complete database from scratch.

There are some tools that already propose to do this for you, as I mentioned in this other answer.

Version control in the file

I also use a header in each script, whose version is linked with CMS. This way it is possible to track in each environment which version of the previous one should be in the database according to the version of the system.

An interesting case I’ve worked on is a system that has specific versions of procedures for Oracle and SQL Server. As it had the versions in the header, I added in the script of build system validation to prevent build if the header version of a PL/SQL database was different from the equivalent version in T-SQL.

Finally, this way you can check the consistency of a system installation at any time.

Ferramentas Específicos

The approach presented above is agnostic to the SGBDR you are using.

However, if you work with a specific technology, see the specific version control tools.

For example, Oracle’s SQL Developer integrates with SCM and allows managing scripts from the IDE.

Microsoft provides SQL Server Data Tools for controlling database artifacts in files .sql. see an article in English about this.

The Mysql Workbench has a visual database editor that synchronizes with the server or allows the generation of scripts from the model.

Final considerations

All concepts presented should be used according to the project.

The control requirement varies greatly if you have one or many customers, if you have direct access to the production bank or have intermediation and analysis of DBA’s, etc.

Analyze all techniques and approaches and choose the one that best fits your case.

Answer 2

or save the . sql in a folder for this or edit the old one as

stp_nome_procedure_bkp01
stp_nome_procedure_bkp02
stp_nome_procedure_bkp03

leaving in the database itself, I was already very saved because I had the old code of stp after x in x time I make a clean...

I usually use a header similar to yours and leave a storedprocedure backup in the database itself...some exceptions saved in a folder of their own.

An off topic suggestion is not to use sp_ as a stored prefix, prefix stp_ pq sp_ are stored systems Procedure...so the system first checks there later on yours.. loses some performance