Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

Formatting issue (new line and parentheses)

ogrishmanogrishman Posts: 78 Bronze 4
edited January 13, 2017 4:24AM in SQL Prompt
I have a piece of code and after formatting it using my configuration it looks like this:
INSERT INTO dbo.dept
(   id,
    name
)
VALUES
(
    1,
    N'Administration'
),
(
    2, N'Sale'
),
(
    3, N'HR'
);
(   id,
    name
)
and the parentheses after VALUES should have the same lists and parentheses configuration why id and the first left parenthesis are on the same line? Also why N'Administration' is on a new line and N'Sale' and N'HR' are not? Below is my configuration:

https://www.dropbox.com/s/7pxbvwntac4ia ... 2.rar?dl=1

Thanks.

Comments

  • My thoughts are the same...

    The code are much longer now...

    How to fix ?
  • ogrishmanogrishman Posts: 78 Bronze 4
    Tdoctor828 wrote:
    My thoughts are the same...

    The code are much longer now...

    How to fix ?


    I have no idea. I hope one of the RedGate support can help me on it.
  • Hi ogrishman

    Thanks for reporting this issue. Sorry you're having problems.

    It seems like the parentheses options are clashing with the list options. Please could you post the code sample in the format you'd like it to be in? We can then make sure we get it working as expected.

    Have you noticed this problem in any other statement types or is it just inserts?

    Many thanks
    Harry
  • ogrishmanogrishman Posts: 78 Bronze 4
    I hope it can be formatted to this:
    INSERT INTO dbo.dept
        (id, name)
    VALUES
        (1, N'Administration'),
        (2, N'Sale'),
        (3, N'HR');
    

    Or at least this:
    INSERT INTO dbo.dept
    (id, name)
    VALUES
    (1, N'Administration'),
    (2, N'Sale'),
    (3, N'HR');
    

    or this:
    INSERT INTO dbo.dept
    (   
        id, name
    )
    VALUES
    (
        1, N'Administration'
    ),
    (
        2, N'Sale'
    ),
    (
        3, N'HR'
    );
    


    I didn't test throughly and I just happen to found this issue. Thanks harryf.
  • Hi

    This is also a problem with Select

    SELECT
    ISNULL(SUM(Qty),
    0)
    FROM
    dbo.TB_NAVI_Bin_Content
    WITH ( READUNCOMMITTED )
    WHERE
    [Item No.] = @itemno
    AND Warehouse = 2
  • Hi Ogrishman

    I've had a go at trying to format the INSERT the way you'd like it. I've created a style file for each of the options you sent. You can download them here: https://files.red-gate.com/link/ShKfBxtVuVEeTYjekzlUyR

    If you extract the files to your styles folder (by default this is C:Users<user>AppDataLocalRed GateSQL Prompt 7StylesV2) they should appear in the formatting styles UI.

    I'm anticipating that these styles will change the formatting of other statements which might not be what you want. Please let us know if that's the case.

    Cheers
    Harry
  • ogrishmanogrishman Posts: 78 Bronze 4
    Hi harryf,

    Thanks for taking time to answer my questions. I tried your formatting files. These three formatting files produce the following output.
    INSERT INTO dbo.dept
        (id, name)
    VALUES
        (1,
         N'Administration'
        ),
        (2, N'Sale'),
        (3, N'HR');
    


    INSERT INTO dbo.dept
    (id, name)
    VALUES
    (1,
     N'Administration'
    ),
    (2, N'Sale'),
    (3, N'HR');
    
    INSERT INTO dbo.dept
    (
        id, name
    )
    VALUES
    (
        1,
        N'Administration'
    ),
    (
        2, N'Sale'
    ),
    (
        3, N'HR'
    );
    

    Note they are not exactly the same as I requested. The
    1, N'Administration'
    
    are always on separate lines. Why they are on separate lines while others are on the same line?

    Regards.
  • Hi ogrishman

    Sorry that didn't help.

    I don't see the same issue when testing against our latest build. Please could you try the formatting files with this version of SQL Prompt (http://download.red-gate.com/EAP/SQLPro ... .0.176.exe)? I think you should then see the same results as me.

    Many thanks
    Harry
  • chujanenchujanen Posts: 3 Bronze 1
    I would also like to have similar formatting.  The link to the styles are no longer available.  Can you please explain the settings or re-upload the style files?
Sign In or Register to comment.