sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | CURSOR KILL

DECLARE @ifKill bit = 1

declare @var table (
 [spid] [smallint] NOT NULL,
 [ecid] [smallint] NOT NULL,
 [status] [nchar](30) NOT NULL,
 [loginame] [nvarchar](128) NULL,
 [hostname] [nchar](128) NOT NULL,
 [blk] [char](5) NULL,
 [dbname] [nvarchar](128) NULL,
 [cmd] [nchar](16) NOT NULL,
 [request_id] [int] NOT NULL

insert into @var
exec sp_who

select spid from @var where spid > 50

if @ifKill <> 1 begin return end

declare cur_ani cursor for
select spid from @var where spid > 50

open cur_ani

declare @i int
declare @str nvarchar(max)
fetch next from cur_ani into @i

while @@FETCH_STATUS = 0 
begin
 begin try 
 set @str = 'kill ' + cast(@i as nvarchar(max))
 print @str
 exec (@str)
 end try begin catch SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE() end catch
 fetch next from cur_ani into @i
end 
close cur_ani
deallocate cur_ani
Comments