Rewrite a Cursor

Hi Lee Markum ,

To re-write a cursor try like this :

DECLARE   @i INT = 1,
                  @spid INT,
                  @Count INT,
                  @kill INT 
SELECT IDENTITY(INT) "n" ,
       spid ,
       loginame
INTO ##temp
FROM sys.sysprocesses
WHERE (db_name(dbid) = N'AdventureWork2012')
AND (program_name = N'Microsoft Dynamics NAV Classic client')
-- number of seconds idle -- remember this does not mean there isn't a long job running
-- 3600 is 1 hour
AND (datediff(ss, last_batch, getdate()) > 3600)
AND spid > 50 -- getting only user processes
SELECT @Count = MAX(n)
FROM ##temp
SET @kill = 0 -- Set 0 to PRINT , 1 to EXECUTE
WHILE (@i <= @Count)
  BEGIN
        SELECT @spid = spid FROM ##temp WHERE n = @i
          IF @kill = 0 
           BEGIN
             PRINT 'KILL '  + @spid
           END
          ELSE
           BEGIN
              EXEC ('KILL '  + @spid )
           END
        
        SET @i = @i + 1
   END
 --DROP TABLE ##temp
  

Here are few related reference links :

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx

http://www.techrepublic.com/blog/the-enterprise-cloud/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f1338d43-5b99-4be8-b8e3-2bba2b4b309b/i-need-help-understanding-the-difference-between-cursors-and-set-based-querying

---

sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.