Formatting your T-Sql Code

I have as maybe some of you a bit of a compulsion. I want to be able to read someone else’s code and not have to strain my eyes to understand it. I also like to be able to go back to code “I“ wrote 6 months or a year or 5 years ago and still be able to make heads or tails of it. If you like your code a scrunched together and aren’t worried about figuring it out later on I am happy for you. For the rest of us mear mortals it is much less rude to at least through some carriage returns into the code to break it up.  I’ll give an example of what I think is some badly formatted code and then show how I would format it.

BAD:
–~~~~~~
Else
BEGIN
Insert Into @Temp_ApprovalProcess
Select a.FieldID,a.FieldValue,a.FieldOrder,a.ActionID,aa.ActionDesc,a.ShowAction,a.ProcedureName
From dbo.tbl_ApprovalProcess a (NOLOCK), tbl_ApprovalAction aa (NOLOCK)
Where a.ActionID=aa.ActionID AND LEFT(a.StoreID,5)=LEFT(@StoreID,5) AND a.ApprovalOrder=3  AND a.IsDeleted = 0
AND a.IsActive=1 AND (@ApplicationDate BETWEEN a.ValidFrom AND a.ValidTo) AND Coalesce ( a.IsAllState,1)=1 AND Coalesce ( a.LoanType,@LoanType)=@LoanType
ORDER BY a.FieldOrder
Insert Into @Temp_ApprovalProcess
Select a.FieldID,a.FieldValue,a.FieldOrder,a.ActionID,aa.ActionDesc,a.ShowAction,a.ProcedureName
From dbo.tbl_ApprovalProcess a (NOLOCK), tbl_ApprovalAction aa (NOLOCK)
Where  a.ActionID=aa.ActionID AND LEFT(a.StoreID,5)=LEFT(@StoreID,5)  AND a.ApprovalOrder=3 AND a.IsDeleted = 0
AND a.IsActive=1 AND (@ApplicationDate BETWEEN a.ValidFrom AND a.ValidTo) AND Coalesce ( a.IsAllState,1)=0 AND Coalesce ( a.LoanType,@LoanType)=@LoanType
AND @CustState IN(Select StateCode From dbo.tbl_ApprovalProcessState aps (NOLOCK) Where a.ApprovalID=aps.ApprovalID)
ORDER BY a.FieldOrder
END

–~~~~~~

While this isn’t completely horrible in the grand scheme of things imagine trying to find it in amongst an (If .. Else) cascade of code that runs on and on for over 2000 lines even as it is. Not fun.
Having a bit of a sense of self preservation and though they may deny it some compassion for my coworkers I would use my favorite text editor to reformat the code to look something like what is below. Before you decide to lynch me for other issues that are much more glaring in the code I will remind you that the blog today is about Code Formatting. I’ll address the other issues in blogs to come.

Better:
–~~~~~~

Else
Begin
    Insert Into @Temp_ApprovalProcess
    Select  a.FieldID
        ,   a.FieldValue
        ,   a.FieldOrder
        ,   a.ActionID
        ,   aa.ActionDesc
        ,   a.ShowAction
        ,   a.ProcedureName
    From    dbo.tbl_ApprovalProcess a WITH (NOLOCK)
        ,   dbo.tbl_ApprovalAction aa WITH (NOLOCK)
    Where   a.ActionID = aa.ActionID
        AND Left ( a.StoreID, 5 ) = Left ( @StoreID, 5 )
        AND a.ApprovalOrder = 3
        AND a.IsDeleted = 0
        AND a.IsActive = 1
        AND @ApplicationDate BETWEEN a.ValidFrom AND a.ValidTo
        AND Coalesce ( a.IsAllState, 1 ) = 1
        AND Coalesce ( a.LoanType, @LoanType ) = @LoanType
    Order By    a.FieldOrder;

    Insert Into @Temp_ApprovalProcess
    Select  a.FieldID
        ,   a.FieldValue
        ,   a.FieldOrder
        ,   a.ActionID
        ,   aa.ActionDesc
        ,   a.ShowAction
        ,   a.ProcedureName
    From    dbo.tbl_ApprovalProcess a WITH (NOLOCK)
        ,   dbo.tbl_ApprovalAction aa WITH (NOLOCK)
    Where   a.ActionID = aa.ActionID
        AND Left ( a.StoreID, 5 ) = Left ( @StoreID, 5 )
        AND a.ApprovalOrder = 3
        AND a.IsDeleted = 0
        AND a.IsActive = 1
        AND @ApplicationDate BETWEEN a.ValidFrom AND a.ValidTo
        AND Coalesce ( a.IsAllState, 1 ) = 0
        AND Coalesce ( a.LoanType, @LoanType ) = @LoanType
        AND @CustState IN ( Select  StateCode
                            From    dbo.tbl_ApprovalProcessState aps WITH (NOLOCK)
                            Where   a.ApprovalID=aps.ApprovalID
                            )
    Order By    a.FieldOrder;
End

–~~~~~~

I’ll leave you with this tidbit…  The Sql Server data engine and the query optimizer to not care about spaces. They do not cause your code to take up more room in memory.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s