SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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:


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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...