SQL Complete (2.50.84): Comment Formatting Issues

[daveb]

I've very impressed with how you suggestions work but there are a few issues with your formatting especially with comments.

1. Inline comments for parameters of a stored procedure are moved to a new line after formatting.
Before
=====

Code: Select all

create procedure foobar( param1 int, -- comment 1
                         param2 int )

After
====
create procedure foobar( param1 int,
-- comment 1
                         param2 int )

2. Comments that follow a begin - end block and are associated with the next line are moved to the line containing the end statement after formatting (see below).

Code: Select all

Before formatting
============
if 1 = 1
begin
    select something
end

/* A comment */
select somthingelse

After formatting
===========
if 1 = 1
begin
    select something
end /* A comment */

select somethingelse

3. I would like to be able to indent join statements in a select statement like shown:

Code: Select all

select table1.a
from  table1
      join table2  on table1.a = table2.a

The word join aligns under table1. However, I don't want the join statements to continue "chaining" just indent.

Thanks,
Dave

[.jp]

Thanks for the post.
1, 2: Yes, these are bugs, and we'll fix them in the next build.
3: Please set "Indent JOIN clauses in JOIN chains" option (SSMS -> SQL Complete -> Formatting -> Indentation -> SQL Statements). Does it help?

[.jp]

1, 2: Yes, these are bugs, and we'll fix them in the next build.

The first case we have already reproduced.
But we need a bit more information to reproduce the second case. Could you specify the exact formatting options you set?

[daveb]

To reproduce #2 start with this code. You need the initial begin / end block.

Code: Select all

BEGIN
IF 1 = 1
BEGIN
	SELECT 1
END 

/* A comment */
SELECT 2
END

The settings I used are stored in this REG file.
support.telog.com/downloads/SqlServerFormatting.reg

The results I get are:

Code: Select all

BEGIN
	IF 1 = 1
	BEGIN
		SELECT 1
	END /* A comment */
	SELECT 2
END

-Dave

[daveb]

As for my #3, I have tried your chained join option and that helps. Ideally, I'd like an option to indent the first join. When chaining is enabled, the first join is aligned with the FROM statement and I would like it to be indented. Here is what your option current returns:

Code: Select all

SELECT site_name, measurement_name
FROM dbo.sites s
INNER JOIN dbo.measurements m ON m.site_id = s.site_id
	INNER JOIN dbo.measurement_parameters mp ON mp.measurement_id = m.measurement_id

Here is what I would like. Note the alignment of the columns, table and joins. I don't personally like my joins to chain.

Code: Select all

SELECT site_name, measurement_name
FROM	dbo.sites s
		 INNER JOIN dbo.measurements m ON m.site_id = s.site_id
		 INNER JOIN dbo.measurement_parameters mp ON mp.measurement_id = m.measurement_id

Formatting is a hard business because everyone want something just a little different. Thanks for your responses!

-Dave

[daveb]

Let add a #4. Here is the type of statement we write a lot of. No one formats this well. Try running it through your formatting algorithm and see what happens. I suggesting trying it with line wrapping disabled and then with it enabled. Do you have any suggestions?

Code: Select all

SELECT  dbo.fUtilConvertToSTD(dateadd(s, (datediff(s, '1/1/2000', dbo.fUtilConvertToDST(td.trend_data_time, @tz, @enable_dst)) / @compress_by) * @compress_by, '1/1/2000'), @tz, @enable_dst) [TIME],
        SUM(case when m.measurement_type_id in(4,18) then td.trend_data_avg * td.trend_data_interval else null end) / SUM(case when m.measurement_type_id in(4,18) then td.trend_data_interval else null end) [DEPTH (in)],
        SUM(case when m.measurement_type_id IN(3,17) then td.trend_data_avg * td.trend_data_interval else null end) / SUM(case when m.measurement_type_id in(3,17) then td.trend_data_interval else null end) [VELOCITY (fps)],
        SUM(case when m.measurement_type_id in(2,16) then td.trend_data_avg * td.trend_data_interval else null end) / SUM(case when m.measurement_type_id in(2,16) then td.trend_data_interval else null end) [FLOW (mgd)],
        SUM(case when m.measurement_type_id in(2,16) then td.trend_data_avg * td.trend_data_interval / 86400 else null end)  [CUMM FLOW (mg)],
        SUM(case when m.measurement_type_id in(6) then td.trend_data_avg else null end)  [RAIN (in)]
FROM    dbo.measurements m
INNER JOIN dbo.trend_data td on m.measurement_id = td.measurement_id
        WHERE   m.measurement_id in (SELECT mid from @mids)
        AND   td.trend_data_time >= @starttime and   td.trend_data_time <  @endtime
GROUP BY  dbo.fUtilConvertToSTD(dateadd(s, (datediff(s, '1/1/2000', dbo.fUtilConvertToDST(td.trend_data_time, @tz, @enable_dst)) / @compress_by) * @compress_by, '1/1/2000'), @tz, @enable_dst)
ORDER BY  [TIME]

[.jp]

Let add a #4

Please give us an example of formatting, how you want this statement to be formatted. We will suggest you which options need to be set, and if it will not be enough, we will study the possibility of adding new options.
By the way, what your SQL Complete Edition is?

[daveb]

The code is formatted (manually). Just select the contents of the code box from the forum post and paste it into SSMS. It should format nicely although the lines are too long. Then run your Format command and see what happens

-Dave

[.jp]

> To reproduce #2
Alas, we could not reproduce this bug in this situation and with these very settings.
But we reproduced a similar bug in several other situations, and we will fix it.

> As for my #3
I have logged this in our internal tracking system as a suggestion.

> Let add a #4
Here is what I've got after formatting.

Code: Select all

SELECT dbo.fUtilConvertToSTD(dateadd(s, (datediff(s, '1/1/2000', dbo.fUtilConvertToDST(td.trend_data_time, @tz, @enable_dst)) / @compress_by) * @compress_by, '1/1/2000'), @tz, @enable_dst) [TIME]
	 , sum(
	   CASE
	   WHEN m.measurement_type_id IN (4, 18) THEN
		   td.trend_data_avg * td.trend_data_interval
	   ELSE
		   NULL
	   END) / sum(
	   CASE
	   WHEN m.measurement_type_id IN (4, 18) THEN
		   td.trend_data_interval
	   ELSE
		   NULL
	   END) [DEPTH (in)]
	 , sum(
	   CASE
	   WHEN m.measurement_type_id IN (3, 17) THEN
		   td.trend_data_avg * td.trend_data_interval
	   ELSE
		   NULL
	   END) / sum(
	   CASE
	   WHEN m.measurement_type_id IN (3, 17) THEN
		   td.trend_data_interval
	   ELSE
		   NULL
	   END) [VELOCITY (fps)]
	 , sum(
	   CASE
	   WHEN m.measurement_type_id IN (2, 16) THEN
		   td.trend_data_avg * td.trend_data_interval
	   ELSE
		   NULL
	   END) / sum(
	   CASE
	   WHEN m.measurement_type_id IN (2, 16) THEN
		   td.trend_data_interval
	   ELSE
		   NULL
	   END) [FLOW (mgd)]
	 , sum(
	   CASE
	   WHEN m.measurement_type_id IN (2, 16) THEN
		   td.trend_data_avg * td.trend_data_interval / 86400
	   ELSE
		   NULL
	   END) [CUMM FLOW (mg)]
	 , sum(
	   CASE
	   WHEN m.measurement_type_id IN (6) THEN
		   td.trend_data_avg
	   ELSE
		   NULL
	   END) [RAIN (in)]
FROM
	dbo.measurements m
INNER JOIN dbo.trend_data td
	ON m.measurement_id = td.measurement_id
WHERE
	m.measurement_id IN (SELECT mid
						 FROM
							 @mids)
	AND td.trend_data_time >= @starttime
	AND td.trend_data_time < @endtime
GROUP BY
	dbo.fUtilConvertToSTD(dateadd(s, (datediff(s, '1/1/2000', dbo.fUtilConvertToDST(td.trend_data_time, @tz, @enable_dst)) / @compress_by) * @compress_by, '1/1/2000'), @tz, @enable_dst)
ORDER BY
	[TIME]

Of course, I used default settings and did not change anything. The only problem we can see is a long line that was not formatted, i.e. the “dbo.fUtilConvertToSTD(dateadd(s, (datediff …” line. Maybe we should consider adding more options in such cases. If you have any suggestions concerning formatting of this script, please tell us.

Also we plan to add some predefined formatting templates to avoid setting such a great amount of options manually.

[daveb]

Ah, it turned out to be an issue only when I turned off wrapping for "Stack SELECT List Items". Thanks for looking at it.

One more note on my item #2. The problem with the comments does not happen if you enable the "Keep user line breaks in SQL expressions" option. So that seems to be a workaround.

-Dave

[gw123]

As for my #3, I have tried your chained join option and that helps. Ideally, I'd like an option to indent the first join. When chaining is enabled, the first join is aligned with the FROM statement and I would like it to be indented. Here is what your option current returns:

Code: Select all

SELECT site_name, measurement_name
FROM dbo.sites s
INNER JOIN dbo.measurements m ON m.site_id = s.site_id
	INNER JOIN dbo.measurement_parameters mp ON mp.measurement_id = m.measurement_id

Here is what I would like. Note the alignment of the columns, table and joins. I don't personally like my joins to chain.

Code: Select all

SELECT site_name, measurement_name
FROM	dbo.sites s
		 INNER JOIN dbo.measurements m ON m.site_id = s.site_id
		 INNER JOIN dbo.measurement_parameters mp ON mp.measurement_id = m.measurement_id

Formatting is a hard business because everyone want something just a little different. Thanks for your responses!

-Dave

This is exactly what my coworkers and I would like as well. Looking at some StackOverflow answers, this is how many SQL devs format it too (without chaining). Thanks!

[.jp]

to daveb,

We fixed problems with formatting comments in BEGIN...END block.
Please download the new build of SQL Complete, v2.50.86 with fixes.

[daveb]

Thanks. I tied it last night and it looks good.

[Elias]

Code: Select all

SELECT site_name, measurement_name 
FROM   dbo.sites s 
       INNER JOIN dbo.measurements m ON m.site_id = s.site_id 
       INNER JOIN dbo.measurement_parameters mp ON 
p.measurement_id = m.measurement_id

Hello. Glad to announce that we have implemented this style of formatting. It will be available in the new version of SQL Complete soon.

[daveb]

Thanks. Be happy to try it when its released.

-Dave