ms-sql: if statements that aren't closed explicitly

[pank]

Many thank for making sql indent. It's a great tool!

I have the pleasure(?) of working with transact-sql/ms-sql which is ... an interesting sql dialect.

One pattern that seems very common is to use temp tables like this (from here)

-- first procedure
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL  
  DROP TABLE dbo.Gloves;
-- end if is supported
GO -- optional
-- second procedure
SELECT ProductModelID, Name  
  INTO dbo.Gloves  
  FROM Production.ProductModel  
 WHERE ProductModelID IN (3, 4);  
GO  -- optional

Unfortunately, sql-indent gets confused with the non-terminated if (and the ms sql server barfs at end if). sql-indent indentation from emacs -q [having evaled (setq sql-product 'ms)]:

-- first procedure
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL  
  DROP TABLE dbo.Gloves;
  -- end if is supported
  GO -- optional
     -- second procedure 
    SELECT ProductModelID, Name  
    INTO dbo.Gloves  
    FROM Production.ProductModel  
    WHERE ProductModelID IN (3, 4);  
  GO  -- optional

In more complex examples, I've also triggered the bad closing for if/case block error, but I can't seem to reproduce this with a simple example.

I know that you (understandably) deprioritise ms-sql so it is fair if this is outside the scope of sql-indent. OTOH, if you want, I can keep reporting ms-sql misbehaviours.

[alex-hhh]

Thanks for reporting this, I pushed a fix on the "ah/pr107" branch (see #108), can you please test it out and let me know if it fixes the problem for you?

Thanks, Alex.

[pank]

I tested it quickly and it seems to work better.

Two observations

• in the original example the comment "-- second procedure" is still indented. Not sure if that is a bug. i.e.

  -- end if is supported
  GO -- optional
   -- second procedure

  instead of

  -- end if is supported
  GO -- optional
  -- second procedure

  I don't know which is more correct or if it can be fine-tuned via the indentation preferences.

• If the semicolon is left out, as it often the case with ms-sql, the indention continues. For example,

  -- first procedure
  IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp -- ended implicitly 
  -- second procedure
  SELECT foo
  INTO #tmp
  FROM  bar

  is formatted as

  -- first procedure
  IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp -- ended implicitly 
  -- second procedure
  SELECT foo
  INTO #tmp
  FROM  bar

  It might be fine to keep it like this as it forces more explicit SQL code.

[alex-hhh]

The comment indentation is intended behavior; however, you can create a custom indentation style based on your preferences. For example, to indent comments at the same level as normal code, you can use a value of 0 for 'comment-start -- the file sql-indent.org goes into detail on how to define your own indentation style.

Regarding wrong indentation when the semicolon is missing, I'm not sure I can handle this in sql-indent.el -- this would require a full SQL parser, since it needs to be able to tell if the line ends on a complete SQL statement or not. sql-indent.el uses a variety of heuristics to quickly determine where a statement starts and looking for semicolons is one of those heuristics.

Perhaps an indentation engine based on treesitter or other language servers would be able to handle it, but that would be a separate project, and I don't have the time to work on that.