Formatting cursor logic indents weird

Mindflux

Here's a kill user script I found on the web a long time ago, I've flattened it out to show what it looks like before SQL Prompt format

DECLARE @strSQL VARCHAR(255)

--PRINT 'Killing Users'
--PRINT '-----------------'
CREATE TABLE #tmpUsers (
spid INT,
eid INT,
STATUS VARCHAR(30),
loginname VARCHAR(50),
hostname VARCHAR(50),
blk INT,
dbname VARCHAR(50),
cmd VARCHAR(30),
request_id INT
)

INSERT INTO #tmpUsers
EXEC SP_WHO

DECLARE LoginCursor CURSOR READ_ONLY
FOR
SELECT spid,
	dbname
FROM #tmpUsers
WHERE dbname = @dbname

DECLARE @spid VARCHAR(10)
DECLARE @dbname2 VARCHAR(40)

OPEN LoginCursor

FETCH NEXT
FROM LoginCursor
INTO @spid,
	@dbname2

WHILE (@@fetch_status <> - 1)
BEGIN
IF (@@fetch_status <> - 2)
BEGIN
	--PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
END
FETCH NEXT
FROM LoginCursor
INTO @spid,
	@dbname2
END

CLOSE LoginCursor

DEALLOCATE LoginCursor

DROP TABLE #tmpUsers
	--PRINT 'Done'

Here's the same script after formatting it with SQL Prompt:

    DECLARE @strSQL VARCHAR(255)

--PRINT 'Killing Users'
--PRINT '-----------------'
    CREATE TABLE #tmpUsers
        (
          spid INT ,
          eid INT ,
          STATUS VARCHAR(30) ,
          loginname VARCHAR(50) ,
          hostname VARCHAR(50) ,
          blk INT ,
          dbname VARCHAR(50) ,
          cmd VARCHAR(30) ,
          request_id INT
        )

    INSERT  INTO #tmpUsers
            EXEC SP_WHO

    DECLARE LoginCursor CURSOR READ_ONLY
    FOR
        SELECT  spid ,
                dbname
        FROM    #tmpUsers
        WHERE   dbname = @dbname

    DECLARE @spid VARCHAR(10)
    DECLARE @dbname2 VARCHAR(40)

    OPEN LoginCursor

    FETCH NEXT
FROM LoginCursor
INTO @spid, @dbname2

    WHILE ( @@fetch_status <> -1 ) 
        BEGIN
            IF ( @@fetch_status <> -2 ) 
                BEGIN
	--PRINT 'Killing ' + @spid
                    SET @strSQL = 'KILL ' + @spid
                    EXEC (@strSQL)
                END
            FETCH NEXT
FROM LoginCursor
INTO @spid, @dbname2
        END

    CLOSE LoginCursor

    DEALLOCATE LoginCursor

    DROP TABLE #tmpUsers
	--PRINT 'Done'

The "Fetch next" are the parts I find odd. Fetch Next gets indented away from FROM LoginCursor INTO ....

then the second fetch next before the END code gets formatted even further out, I guess because it's part of the indent logic for the WHILE BEGIN/END BLOCK?

Chris Auckland

Thanks for your post.

I'm afraid we don't have support for formatting FETCH NEXT statements, which is why the behavior isn't completely predictable.

I've added you as another vote for SP-3019.