A simple but effective trick for working with SQL comment blocks

Developers of systems using databases have to write a lot in SQL. Everyone knows, but not everyone realizes that SQL has been going through its fourth decade as one of the most successful and widespread technologies in the computer world. Technologies do not stand still, but even today, many creators of post-relational database systems specifically invest in resources and resources to provide users with SQL-like means of searching and manipulating data. Let's look at how modern requirements for database development products make it easier and faster to create the correct SQL code and get acquainted with a curious little trick.

Recently I came across a simple and effective solution to one elementary, not even a problem, but inconvenience, and decided to share. The bottom line is this:

Like any practicing SQL developer or DBA, I save scripts to solve repetitive tasks so that in the future I already have a prepared tool for quick execution. Using DBArtisan, you can automatically record all the SQL statements that I executed during the session and then use some of them to create and save such scripts. In the DBArtisan environment, I can put items in the main menu to call the most commonly used scripts or run the script on several servers at the same time.

Naturally, many of these repetitive tasks require different specialized “pieces of code”, depending on the problem being solved or the database. It turned out that it is often faster and easier to support not to create a lot of the same type of “almost” matching SQL scripts or versions, but to use “block comments” to temporarily turn off / on the necessary SQL fragment and manually manage them in the ISQL editor.

Everyone knows that the SQL standard provides 2 types of comments in the source code:

• "Lowercase" that begin with the characters '-', and turn everything to the end of this line into text that the SQL translator does not accept. When moving to the next line, this comment is no longer valid
• "Block", which can be turned into a comment a large block of SQL-code. They begin with the combination of characters '/ *' and end as soon as the combination '* /' is encountered. These symbols indicate “beginning of block” and “end of block”

The trick, on the example of which I will talk about the capabilities of modern ISQL editors, was proposed by  Peter Zerk - one of the famous experts in MS SQL Server. Take the SQL text with the commented out piece:

while (@j < @nstr)
begin  -- цикл по строкам массивов
   INSERT INTO PRR.TRANSITADVOLS ( VAL_ID,VAL_MASH )
   VALUES
   /* здесь может быть блок любого текста произвольной длины,
       который не будет выполняться сервером,
       ...
       ... обратите внимание на "строчный" синтаксис комментария в начале следующей строки
   --*/
                (@USERNM,@BDATE);

It would seem that nothing has changed, but putting the “end of the block” in the “lowercase” comment gives us the opportunity to activate this entire block (when we decide that we need to execute it) not to search and delete both the beginning and the end of the block, but just put the characters '-' in front of the "start block" combination

/*
    выключенный блок
    исходного текста
--*/
--/*
    включенный блок
    исходного текста
--*/

If we didn’t put the “end of the block” inside the line comment, the script would be stopped and we would get the error Incorrect syntax near '*'

One can only wonder why my colleagues and I passed this decision in due time.

Now we can localize similar, but specific pieces of code in one place, manage our code.

-- Блоки помогают управлять спецификой аналогичного кода
/* --SCRIPT 'A' - ЭТА ЧАСТЬ используется для вх. дат в формате mm/dd/yyyy
...
.. сюда поместите код для поддержки дат в формате mm/dd/yyyy
...
--*/
/* -- SCRIPT 'B' - ЭТА ЧАСТЬ используется для вх. дат в формате dd/mm/yyyy
...
.. сюда поместите код для поддержки дат в формате dd/mm/yyyy
...
--*/

Suppose I need to process the input with dates in the "American" format - 'mm / dd / yyyy'. I insert the characters - before the first group “block start” and the execution of the SCRIPT A block is turned on. At the same time, SCRIPT B remains off. If the data came in with the date in “our” format - dd / mm / yyyy - then I do the opposite, put it before the second “beginning of the block”, and the SCRIPT block is turned on. B

Tracing

Using the same trick, T-SQL allows us to use another useful "side effect".

-- Сообщите при помощи PRINT, какой блок текста активен 
--/* --SCRIPT 'A' - ЭТА ЧАСТЬ используется для целых данных
Print 'Script A active - integer data'
--*/ Print 'Script A inactive
/* -- SCRIPT 'B' - ЭТА ЧАСТЬ используется для символьных
Print 'Script B active - char data'
--*/ Print 'Script B inactive'

A trace appears in the message window: And all this is achieved by commenting on one line of the source text. Debugging Have you ever understood why a query with a complex set of selection conditions does not work at all as expected? Now it’s easier to do this, by sequentially checking the operability of all or part of these conditions

SELECT * FROM #table
/* -- Сложный набор условий с непроверенными результатами
WHERE ID in ( SELECT something
                            FROM a-really-complex-condition
                           WHERE we-are-not-sure-what-we-get
                         )
--*/  WHERE ID = 1 -- В работоспособности этого условия мы уже убедились

Or control the depth of data modification during script execution

update #table
set Comment = 'changed'
where Comment = 'change me'
/* -- use 'line comment' to enable UPDATE to proceed
            and 1 = 1
--*/ and 1 = 0

This example allows me to debug the entire script without actually modifying it until I verify that everything is working as it should.

And the last one.

Dear author, gave an example of using this trick to support blocks of comments embedded in one another.

SELECT * FROM Mytable
   WHERE 1 = 1 -- always true
   /* -- first condition to check
                 AND condition1 = TRUE
   --*/
  /* -- some more conditions
                 AND condition2 = ( SELECT condition3
                                                    FROM Myanothertable
                                                   WHERE 1 = 1
        /* -- Paying attention? This is a nested Comment Block
                                                        AND NestedCondition = TRUE
        --*/
        )
--*/
--/* the comment here ‘activates' this condition. It gets checked.
              AND Comment = 'buggy value'
--*/

You can see for yourself, it really works. In any ISQL editor, for example, run DBArtisan, and copy this text there.

SQL is a universal, if I may say so, standardized language, which, nevertheless, is transformed in each DBMS in accordance with the requirements of the creators of this platform. In MS SQL, this is a T-SQL dialect; in Oracle, we use PL / SQL; in other platforms, we introduce our own unique syntax and behavior. Here's what it looks like for MS SQL: I decided to apply the trick I liked to work with Oracle. You can additionally run the tools supplied with Oracle, but you can stay in DBArtisan, which supports simultaneous work with different DBMS platforms.





In DBArtisan, I opened another window of the SQL editor, indicated that this editor is already connected to the Oracle database and transferred the script from the example above to it: I just inserted the query text. See the red mark at line 13? This DBArtisan automatic parser signals that the SQL text does not comply with the rules of this SQL dialect. A check on the Oracle site confirmed that, despite the graphical syntax diagrams in the documentation, Oracle did not support and does not support nested comments. By the way, the rest of the examples worked!






The automatic parser / validator is one of the three components that were included in the DBArtisan SQL editor, in accordance with modern requirements to accelerate the development of SQL programs and improve their quality. It also detects cases of using the names of objects that are not in the database, right as you type.

It also helps a lot when developing SQL programs, greatly speeds up and avoids the simplest mistakes in writing the Code Completion mode. If you had to write in modern programming languages ​​in rapid application development environments, for example, Delphi or C ++ Builder, Java Designer or Eclipse, you are familiar with it: as you type, the editor selects objects that are suitable for the context (not only syntax elements ) and substitutes them in the text for you or offers to make a choice from the list. DBArtisan selects objects from the current context based on the DBMS editor platform, from a list of really existing schema objects of the data source used.

If, when writing complex SQL code, it suddenly becomes clear that you need to make some changes to other objects of the circuit, for example, add an input parameter to the stored procedure, you can simply proceed to its editing (in a specialized dialog editor) by highlighting its name directly in text in SQL and by calling with the mouse the appropriate Hyperlink Object Action. Like any text, SQL program scripts can be created in any text editor. But if you are a professional, you work a lot and often work with SQL, then syntax highlighting and automatic code reformatting will not be enough for you, especially if you have to switch between various DBMS platforms.





Moreover, more or less successful attempts are known to make the development technology of the server part of information systems more flexible, more adapted to make changes and implement new requirements, to a greater extent take advantage of the collective development and reuse of successful and proven solutions. For a long time, many DBMSs and solutions for developers have the ability to apply the "versioning" of texts in SQL. About it - in the following articles.