SQL Format is invalidating JOIN statements

dwilliamson

Running Format SQL is invalidating a previously valid statement. So far I've only encountered this on when joining tables.

Example scenario:
Setting up a sample table

DECLARE @TestTable TABLE
	(
		ID BIGINT IDENTITY PRIMARY KEY
		,Value VARCHAR(MAX)
	)

INSERT INTO @TestTable (Value)
VALUES	('Test'),('Test'),('Test')
		,('Test'),('Test'),('Test')
		,('Test'),('Test'),(NULL)

SQL Statement prior to formatting

SELECT	* 
FROM	@TestTable AS tt
	JOIN	@TestTable AS tt2
		ON	tt.ID = tt2.ID
			AND (tt.ID IN (1,2,3) OR tt.Value IS NULL)

SQL Statement after formatting (notice the IS NULL condition)

SELECT	*
FROM	@TestTable AS tt
	JOIN @TestTable AS tt2
		ON tt.ID = tt2.ID
		   AND
		(
			tt.ID IN
	(
		1, 2, 3
	)
			OR   tt.ValueIS NULL
		)

Ali D

Hi

Thanks for reporting this issue. Unfortunately, I haven’t been able to reproduce this behaviour here. Are you using the latest version of SQL Prompt (v7.3.474)? If not, you can download it from here.

If the issue is still happening in the latest version, please could you send the style file responsible to SQLPromptTeam@red-gate.com ?

To find the style file, use the bar at the top of style browser labelled "style folder" (by default it’s saved in %localappdata%Red GateSQL Prompt 7StylesV2 ).

The style file will have the same name as your formatting style and the extension “.sqlpromptstylev2”.

Thanks
Ali

dwilliamson

I was using version 7.3.0.473, but just issue was recreated with Version 7.3.0.474. I'll send the style file over ASAP.

Harry Frankish

Hi dwilliamson

Thanks for sending the file over. We’ve reproduced the issue and will look into it.

Other than the missing space between “tt.Value” and “IS”, is the statement formatted as you would like?

Cheers
Harry

David Priddle

Hi dwilliamson,

We've got a fix for this in the latest beta, which you can download from here or from Check for updates.

Please let us know if it solves your problem!

Best regards,

David