Disabling #sqlprompt Formatting

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)
  ( '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
    '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.

2017-01-03 12_35_05-SQLQuery1.sql - (local)_SQL2016.sandbox (PLATO_Steve (67))_ - Microsoft SQL ServIf 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.

2017-01-03 12_35_14-SQLQuery1.sql - (local)_SQL2016.sandbox (PLATO_Steve (67))_ - Microsoft SQL Serv

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

2017-01-03 12_35_30-SQLQuery1.sql - (local)_SQL2016.sandbox (PLATO_Steve (67))_ - Microsoft SQL Serv

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

2017-01-03 12_35_36-SQLQuery1.sql - (local)_SQL2016.sandbox (PLATO_Steve (67))_ - Microsoft SQL Serv

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

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.