T-SQL Formatters

,

The other day I was given a truly horribly formatted piece of SQL. Something a bit like this only worse, and with plenty of dynamic SQL.

CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail] 
	
AFTER INSERT, DELETE, UPDATE AS 
	
Begin
	
    DECLARE @Count int;
	
    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
	
        RETURN;
	
    SET nocount on;
	
    begin try
-- If inserting or updating these columns
IF update([ProductID]) OR UPDATE([OrderQty]) or Update([UnitPrice]) OR update([UnitPriceDiscount]) 
-- Insert record into TransactionHistory
begin
    INSERT into [Production].[TransactionHistory]
                ([ProductID],[ReferenceOrderID]
	
    ,[ReferenceOrderLineID]
	
    ,[TransactionType]
	
    ,[TransactionDate]
	
                ,[Quantity]
	
                ,[ActualCost])
	
            SELECT 
	
                inserted.[ProductID]        ,inserted.[SalesOrderID]
	
                ,inserted.[SalesOrderDetailID]
	
                ,'S',GETDATE()                ,inserted.[OrderQty]
	
                ,inserted.[UnitPrice]
	
FROM inserted 
	
INNER join [Sales].[SalesOrderHeader] 
	
ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID];
            UPDATE [Person].[Person] 
	
            SET [Demographics].modify('declare default element namespace 
	
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
	
                replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 
	
                with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")')   from inserted 
                INNER JOIN [Sales].[SalesOrderHeader] AS SOH
	
	ON inserted.[SalesOrderID] = SOH.[SalesOrderID] INNER JOIN [Sales].[Customer] AS C
	
                ON SOH.[CustomerID] = C.[CustomerID]
	    WHERE C.[PersonID] = [Person].[Person].[BusinessEntityID];
	
        END;
        -- Update SubTotal in SalesOrderHeader record. Note that this causes the 
	
        -- SalesOrderHeader trigger to fire which will update the RevisionNumber.
	
        UPDATE [Sales].[SalesOrderHeader]
	

I have a hard time working with code that’s so badly formatted I can’t read it. Normally I’d just format it myself, but this was a good dozen pages long. So what did I do? I ran to twitter screaming for help.

And I got a lot of responses! Here were the recommendations followed by who recommended them:

 

I haven’t tested most of these but from what I understand none of them (for obvious reasons) will handle dynamic SQL and the ones I tested won’t handle the multi-line breaks. For the multi-line breaks I just did a regex search and replace, then used the formatter.

Now, as I was writing this Brent Erik Darling (b) wrote a similar post so I’m including it for completeness. Quick tips for debugging large stored procedures – See tip 1

Rate

Share

Share

Rate