Red Gate forums :: View topic - Formatting cursor logic indents weird
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 5
SQL Prompt 5 forum

Formatting cursor logic indents weird

Search in SQL Prompt 5 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Mindflux



Joined: 22 Aug 2006
Posts: 35

PostPosted: Fri May 24, 2013 4:20 pm    Post subject: Formatting cursor logic indents weird Reply with quote

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:
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:

    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?
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Tue May 28, 2013 10:43 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group