Any harm in using BEGIN / END to organize SQL code?

Question

I am not quite used to writing very long SPs. And I found it become quickly hard to manage when working with those wide tables. Even a simple UPSERT will take very long line or tens of lines of code (I found the col/line style easier to read for me, but working with those very wide tables are really a pain for me). Gradualy I found myself start to use BEGIN/END to wrap those UPSERTs and others basic operations on the same table etc to group them into logical sections. So that in IDE they can be easily fold and unfold, to check and review with comments. Note: I am not wrting them for any control blocks. Just simple BEGIN/END to wrap them into a foldable section.

Will adding a few extra BEGIN/END to group and fold/unfold lenthy code lead to any side effect? Currently I am not seeing any yet... If so what they could they be ? Are there any better way to orgnize those code in SP ?

Oh by folding I mean

CREATE PROCEDURE/MULT_STATEMENT_FUNCTION
...
BEGIN
-- UPSERT QC records
    BEGIN
        UPDATE QC_SECT1 <-- fold[]
        SET COL1=...
            COL2=
            .....
        IF @@ROWCOUNT.... <--fold[ click 1]
        INSERT INTO ... <-Fold[click 2]
        (
            COL1
            ....
        VALUES          <-Fold[click 3]
        (
            ...
    END
    -- calculate distribution
    BEGIN
    DECLARE @GRP1_.....
    DECLARE @GRP2_....
    WITH .....
    ....
    ) AS PRE_CAL1  <-- fold[click 1]
    ) AS PRE_CAL2  <-- fold[click 2]
    ) AS ...       <-- fold[click 3] 
    END
END

I found it very inconvienint to inspect long scripts, Gradually I find myself start to use BEGIN/END. In VSCode it take so many clicks to fold a single CTE statement. BEGIN/END made it a lot easier. But I really want to make sure, I am doing sth wrong.

PS: The grouping columns by line ideas are very valid, I fully agree. Regarding the col/line style. It's not that I am really fond of it. But I guess under my current situation many people probably will prefer it too. The wide tables in vendor db have long prefix to , I'd say ,group the columns. list them vertically helps to align them, so that they can be distingished easier... I guess mine currently is a special case.

Thanks.

user_122718 · Answer

You can do this. It's a good idea to structure code like that because it's integrated with the language and the IDE in a way that comments are not. In C languages I sometimes use {} blocks for that in case it is necessary to have larger methods.

I found the col/line style easier to read for me

If that's easier for you then it makes sense. But maybe it also makes sense to train your eyes to accept a style where many columns are in one line. This saves a tremendous amount of vertical space. More fits on the same screen and clarity increases.

user_2647342 · Answer

No, absolutely not. There is no harm in wrapping blocks of code in BEGIN/END blocks. I have been doing it for 10+ years without consequence. The optimizer basically ignores it unless there is a reason to evaluate the BEGIN/END logic (e.g. for a loop or other "WHILE" condition.)

Using BEGIN/END code blocks in SSMS makes it possible to quickly collapse/expand large blocks of code. Below is some code I was just working on to collect some routine metadata. It's 110 lines of code yet easily readable.

Any harm in using BEGIN / END to organize SQL code?

2 Answers