I love SQL Prompt, especially the new formatting engine. However, it’s not perfect, and there are times I don’t want code reformatted. One great example is when I write INSERT statements. Here’s what I might write:

INSERT dbo.Payments_A
  ( [Month], SerialNumber, DateBegin, DateEnd, paid)
VALUES
  ( 'Mar-15', '0000000000001', '3/16/2015 0:00', '4/10/2015 0:00', 5000.01),
  ( 'Apr-15', '0000000000001', '4/7/2015 0:00' , '4/13/2015 0:00', 0),
  ( 'Apr-15', '0000000000001', '4/10/2015 0:00', '4/30/2015 0:00', 15000.00);

I often build a single row, then I might copy/paste to add the other rows and then manually change the data. When I use my reformat command (CTRL+K, Y), I get this:

INSERT dbo.Payments_A
(
    [Month],
    SerialNumber,
    DateBegin,
    DateEnd,
    paid
)
VALUES
(
    'Mar-15', '0000000000001', '3/16/2015 0:00', '4/10/2015 0:00', 5000.01
),
(
    'Apr-15', '0000000000001', '4/7/2015 0:00', '4/13/2015 0:00', 0
),
(
    'Apr-15', '0000000000001', '4/10/2015 0:00', '4/30/2015 0:00', 15000.00
);

That’s not bad, but it’s hard to read and ends up eating up a bunch of screen space when I try to look at code. What I really want is for that INSERT statement to not be reformatted, even though  I may want the spacing and line feeds in other code.

Fortunately, SQL prompt allows me some control. If I highlight my code, I get a little box to the left of the code. There’s a hand with a finger pointing at it in the image below.

If I hit CTRL, I get a drop down (or I can click). In this box, I can type snippets, or words. Note I’ve typed “dis” below, and I see that one of my options is to disable formatting for the selection.

If I select this, I’ll get comments added to my code that SQL Prompt can read.

Now I can hit CTRL_K, Y, and I’ll get all my code formatted, except what’s inside of the comments.

Watch this and a few more SQL Prompt tips from a short video shown at last year’s SQL in the City:

Filed under: Blog Tagged: Redgate, software development, SQL Prompt, syndicated