Kill the Sql Connection

/*****************************************************************

*** Procedure: sp_KillConnections

*** Usage: sp_KillConnections @dbName = 'Database Name'

*** Description: Drop all connections from a specific database

*** Input: @dbName - REQUIRED - Name of the database

*** Output: Outputs the results of the proccess

******************************************************************/

CREATE PROCEDURE sp_KillConnections

      @dbName VARCHAR(128)

AS

DECLARE @spid varchar(5)

DECLARE @LoginName nvarchar(128)

DECLARE @intErrorCode int

DECLARE @intOk int

DECLARE @intError int

DECLARE @intTotal int

SET @intErrorCode = 0

SET @intOk = 0

SET @intError = 0

SET @intTotal = 0

SELECT @intTotal = Count(sp.spid) FROM master..sysprocesses sp JOIN master..sysdatabases sd ON sp.dbid = sd.dbid WHERE sd.name = @dbName

DECLARE KILL_CONS CURSOR FOR

SELECT CAST(sp.spid as varchar(5)),RTrim(sp.loginame) FROM master..sysprocesses sp JOIN master..sysdatabases sd ON sp.dbid = sd.dbid WHERE sd.name = @dbName

OPEN KILL_CONS

FETCH NEXT FROM KILL_CONS INTO @spid,@LoginName

WHILE @@FETCH_STATUS = 0

BEGIN

      EXEC('Kill '+ @spid + '')

      SELECT @intErrorCode = @@ERROR

      IF @intErrorCode = 0

      BEGIN

            SET @intOk = @intOk + 1

            PRINT 'Process ' + @spid + ' from login ' + @LoginName + ' has been ended.'

      END

      ELSE

      BEGIN

            SET @intError = @intError + 1

            PRINT 'Process ' + @spid + ' from login ' + @LoginName + ' could not be ended.'

      END

      FETCH NEXT FROM KILL_CONS INTO @spid,@LoginName

END

CLOSE KILL_CONS

DEALLOCATE KILL_CONS

PRINT 'Total number of processes from database ' + @dbName + ': ' + CAST(@intTotal as varchar)

PRINT 'Processes ended normally: ' + CAST(@intOk as varchar)

PRINT 'Processes could not be ended: ' + CAST(@intError as varchar)

GO