Inconsistent SQL formatting

Answered

Hello,

Love the product, but am coming from the SSMS / SQL Prompt (RedGate) area. I can't seem to get the SQL formatting to behave consistently no matter what I do.

I've tried all kinds of combinations and options - except for the bizarre indentations happening the remaining SQL formatting is to my liking. But I just can't figure this out.

This is a new stored proc snippet for MSSQL:

1. the set nocount on is oddly indented - can't find any solution for that.

2. After my declarations are done, the next if statement and (3) create temp table are indented.

4. Suddenly, the insert into statement is not indented, as it should be.

5. Back to indentation for some reason?

6. Indentation continues...

 

 

3 comments
Comment actions Permalink

Hello, please provide  a full SQL query as text for this procedure to reproduce the issue

0
Comment actions Permalink

No problem; here is a trimmed down version which still displays all the issues.

You can see where the indenting is simply off - no reason for it to suddenly indent.

Thank you.

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[HRG_tmptest] @person_ids HRG_INTEGER READONLY
AS

/*
proc with issues
*/

SET NOCOUNT ON;

DECLARE @problem_id INT;
DECLARE @msg NVARCHAR(2048);

IF OBJECT_ID(N'tempdb..#pids') IS NOT NULL
BEGIN
DROP TABLE [#pids];
END;

CREATE TABLE [#pids] (
[person_id] INT
);
CREATE INDEX [idx_apoasduishfouiashfuaishfiouh_random] ON [#pids] ([person_id]);

INSERT INTO [#pids] ([person_id])
SELECT [@person_ids].[Value]
FROM @person_ids;

SET @problem_id =
(
SELECT MIN([#pids].[person_id]) AS [person_id]
FROM [#pids]
LEFT JOIN [empprim] ON [#pids].[person_id] = [empprim].[person_id]
WHERE [empprim].[person_id] IS NULL
);

IF @problem_id IS NOT NULL
BEGIN
SET @msg = N'At least one person id is not valid; please correct and re-submit: ' + CONVERT(VARCHAR, @problem_id);
THROW 50000, @msg, 1;
RETURN;
END;


IF OBJECT_ID(N'tempdb..#rep') IS NOT NULL
BEGIN
DROP TABLE [#rep];
END;

CREATE TABLE [#rep] (
[Social] VARCHAR(20) NULL, -- required
[Alternate ID] VARCHAR(10) NULL
);


-- get social and alternate id (person_id)
INSERT INTO [#rep] ([Social], [Alternate ID])
SELECT [person].[soc_sec_no],
CONVERT(VARCHAR, [person].[person_id])
FROM [#pids]
INNER JOIN [person] ON [#pids].[person_id] = [person].[person_id];


-- Final output
SELECT [#rep].[Social],
[#rep].[Alternate ID]
FROM [#rep]
ORDER BY [#rep].[Alternate ID];
0
Comment actions Permalink

I've created an issue on our tracker, please follow and vote.

0

Please sign in to leave a comment.