Questions on structuring T-SQL

Question

I'm learning T-SQL and trying to get my head around how to approach it/best practices. Working through some examples, there are three questions I have:

1) Coming from Python, is there a generally agreed upon style guide (something like PEP 8) or approach for laying out T-SQL or is it more like JavaScript where anything goes?

2) When creating stored procedures and functions I see some authors always use a main BEGIN/END block and others don't. At first I thought you need BEGIN/END if you have more than one statement. However, this doesn't seem to be true because I have seen lengthy stored procedures with no main BEGIN/END. Any thoughts on this?

3) Within stored procedures, some authors seem to like to enclose parts of their code in BEGIN/END blocks. I can't see why you'd do this and wonder if I'm missing something. For example:

CREATE PROCEDURE <NAME> (
  <Parameter List...>
)
AS
BEGIN
    -- Setup:
    -- Declare/initialize variables...

    BEGIN TRY
        BEGIN TRANSACTION

        -- Validity check 1:
        IF @Param1 ...
        BEGIN
            -- Do stuff...
        END

        -- Validity check 2
        IF @Param2 ...
        BEGIN
            -- Do stuff...
        END
        -- Update - added BEGIN/END after if blocks for clarity

        -- Why wrap these statements in a BEGIN/END Block???
        BEGIN
            --Add the entry
            INSERT dbo.JournalClientFamilyChanges (
                    HouseholdMembersID,
                    PreviousClientsID,
                    NewClientsID,
                    ActionTaken,
                    Notes,
                    ModifiedBy,
                    ModifiedDate
                )
            VALUES (
                    @HouseholdMembersID,
                    @PreviousClientsID,
                    @NewClientsID,
                    @ActionTaken,
                    @Notes,
                    @ModifiedBy,
                    SYSDATETIME()
                )

            SET @success =1;
            SET @ErrorStatus ='';
            COMMIT TRANSACTION;
        END
    END TRY

    BEGIN CATCH
        -- Error handling...
    END CATCH
END

Any thoughts appreciated,

--Jim

Answer 1

To answer your question at your own example

    IF @Param1 ...

    -- Validity check 2
    IF @Param2 ...

    -- Why wrap these statements in a BEGIN/END Block???
    BEGIN
        --Add the entry
        INSERT dbo.JournalClientFamilyChanges (
                HouseholdMembersID,
                ...
            )
        VALUES (
                @HouseholdMembersID,
                @PreviousClientsID,
                ... 
            )

        SET @success =1;
        SET @ErrorStatus ='';
        COMMIT TRANSACTION;
    END

There is an IF which will call the code below just under certain circumstances. Without the BEGIN ... END this will be valid for the very next statement only. Using BEGIN ... END will see the whole code block covered by the IF condition (the lines with SET and COMMIT).

Try this out:

DECLARE @SomeInt INT=0;

IF @SomeInt=1
PRINT 'Example 1: hello, this is the first line';
PRINT 'Example 1: hello, the next line';

IF @SomeInt=1
BEGIN
PRINT 'Example 2: hello, this is the first line';
PRINT 'Example 2: hello, the next line';
END

For better readability one would use indents. But this is not relevant for the engine. There are some languages using indents as block marker though, others use paranthesis or any kind of brackets. T-SQL uses BEGIN ... END. Sometimes people use this in larger procedures just to allow collapsing (like the #region in C#)

IF @SomeInt=1
    PRINT 'Example 1: hello, this is the first line';

PRINT 'Example 1: hello, the next line';

IF @SomeInt=1
BEGIN
    PRINT 'Example 2: hello, this is the first line';
    PRINT 'Example 2: hello, the next line';
END

update

Just to reflect the comments below: The OP changed the initial question in a way, that the BEGIN ... END is no longer connected to the IF. So the question shoudl be: Why would one use BEGIN ... END without any functional reason?.

The answer for this is

• To mark some lines of code as a "block"
• ... thus emphasising the functional unit
• Collapsing is a nice side-effect