Formatting cursor logic indents weird

Provides intelligent code completion for SQL Server editors.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, Mike Upton, justin.caldicott, Aaron Law

Formatting cursor logic indents weird

Postby Mindflux » Fri May 24, 2013 4:20 pm

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

Code: Select all
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:

Code: Select all
    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?
Mindflux
 
Posts: 35
Joined: Tue Aug 22, 2006 3:13 pm

Postby Chris Auckland » Tue May 28, 2013 10:43 am

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.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.


Return to SQL Prompt 5

Who is online

Users browsing this forum: No registered users and 0 guests