simoncbirch

How I Present SQL Code in WordPress

I really should get more practice writing blog posts to give me a chance of remembering the torturous process of inserting quite nicely formatted SQL code. I say “quite nicely” because I’ve seen it done better elsewhere, but then again I’ve also seem much worse.

The important bits I’ve got nailed are:

  • My readers can copy SQL code from my blog (it’s not just an image).
  • Colour coding resembles SSMS.
  • Easy for me to customise the height of the box, with user scrolling to access beyond the immediately visible window.
  • Width of the box varies depending on the browser and screen, again with scroll bars presented where necessary.
  • Code is large enough to be legible in the published blog without me having to find my glasses.
  • Code shrinks and grows with rest of blog text (at least in Chrome using CTRL+/-).

I rely on the third party ultility vs2html to convert SQL to html.

  1. Start writing your blog in Visual mode (I’m assuming you’re in WordPress too).
  2. Copy the SQL code from SSMS or Visual Studio to the clipboard.
  3. Run vs2html.exe (there is no output so it looks like it’s done nothing, but it will have replaced the contents of the clipboard with html rendered SQL code).
  4. Switch to HTML mode and paste from clipboard to the desired spot.
  5. Replace the first tag which probably looks like this:
    pre1
    with this:
    pre3
    I often vary the height based on how much real estate I want the SQL to command on the page (and yes, I realise the irony of these being images, but it was too hard to display HTML without WordPress trying to interpret it).
  6. Check it out in Visual mode and then Preview mode – et voila! (My next post might be accenting foreign characters).

Here’s one I prepared earlier:

declare @sinkCount int;
SELECT @sinkCount=count(1) from (SELECT TOP 1 ProductKey FROM dbo.donor WHERE ProductKey = (SELECT TOP 1 ProductKey FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 OrderDateKey FROM dbo.donor WHERE OrderDateKey = (SELECT TOP 1 OrderDateKey FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 DueDateKey FROM dbo.donor WHERE DueDateKey = (SELECT TOP 1 DueDateKey FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 ShipDateKey FROM dbo.donor WHERE ShipDateKey = (SELECT TOP 1 ShipDateKey FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 CustomerKey FROM dbo.donor WHERE CustomerKey = (SELECT TOP 1 CustomerKey FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 PromotionKey FROM dbo.donor WHERE PromotionKey = (SELECT TOP 1 PromotionKey FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 CurrencyKey FROM dbo.donor WHERE CurrencyKey = (SELECT TOP 1 CurrencyKey FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 SalesTerritoryKey FROM dbo.donor WHERE SalesTerritoryKey = (SELECT TOP 1 SalesTerritoryKey FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 SalesOrderNumber FROM dbo.donor WHERE SalesOrderNumber = (SELECT TOP 1 SalesOrderNumber FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 SalesOrderLineNumber FROM dbo.donor WHERE SalesOrderLineNumber = (SELECT TOP 1 SalesOrderLineNumber FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 RevisionNumber FROM dbo.donor WHERE RevisionNumber = (SELECT TOP 1 RevisionNumber FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 OrderQuantity FROM dbo.donor WHERE OrderQuantity = (SELECT TOP 1 OrderQuantity FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 UnitPrice FROM dbo.donor WHERE UnitPrice = (SELECT TOP 1 UnitPrice FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 ExtendedAmount FROM dbo.donor WHERE ExtendedAmount = (SELECT TOP 1 ExtendedAmount FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 UnitPriceDiscountPct FROM dbo.donor WHERE UnitPriceDiscountPct = (SELECT TOP 1 UnitPriceDiscountPct FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 DiscountAmount FROM dbo.donor WHERE DiscountAmount = (SELECT TOP 1 DiscountAmount FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 ProductStandardCost FROM dbo.donor WHERE ProductStandardCost = (SELECT TOP 1 ProductStandardCost FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 TotalProductCost FROM dbo.donor WHERE TotalProductCost = (SELECT TOP 1 TotalProductCost FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 SalesAmount FROM dbo.donor WHERE SalesAmount = (SELECT TOP 1 SalesAmount FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 TaxAmt FROM dbo.donor WHERE TaxAmt = (SELECT TOP 1 TaxAmt FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 Freight FROM dbo.donor WHERE Freight = (SELECT TOP 1 Freight FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 CarrierTrackingNumber FROM dbo.donor WHERE CarrierTrackingNumber = (SELECT TOP 1 CarrierTrackingNumber FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 CustomerPONumber FROM dbo.donor WHERE CustomerPONumber = (SELECT TOP 1 CustomerPONumber FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 OrderDate FROM dbo.donor WHERE OrderDate = (SELECT TOP 1 OrderDate FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 DueDate FROM dbo.donor WHERE DueDate = (SELECT TOP 1 DueDate FROM dbo.donor)) X
SELECT @sinkCount=count(1) from (SELECT TOP 1 ShipDate FROM dbo.donor WHERE ShipDate = (SELECT TOP 1 ShipDate FROM dbo.donor)) X

If I leave it another six months before documenting some SQL, this post will get another click (from me).  If you have any tips on this I’m all ears.

Posted in SQL ServerLeave a comment

Leave a comment