Lock 걸린 프로시저 KiLL 하기 > db

[MS-SQL]에서 [Lock]걸린 프로시저

[Kill]하는 프로시저 입니다.

-----------------------------------------------------------

CREATE PROCEDURE [PZZ0_0_iERP_프로시저_검색_중지]
                                (@DB_ERR                  [INT] OUTPUT,
                                 @USER_SPID             [INT] OUTPUT,
                                 @USER_HOST            [VARCHAR](255) OUTPUT,
                                 @USER_PROGRAM     [VARCHAR](255) OUTPUT)

AS

if exists (select * from tempdb.dbo.sysobjects where id = object_id('[tempdb].[dbo].[#c_Batch_Process]'))
   drop table #c_Batch_Process

DECLARE @spid_1                         [INT]
DECLARE @KiLL_PRO                    [VARCHAR](30)

DECLARE @COMMAND_PROCESS  [VARCHAR](255)
DECLARE @최종작업일자                [VARCHAR](10)
DECLARE @최종작업시간                [VARCHAR](8)

SET @최종작업일자 = CONVERT(VARCHAR(30), GETDATE(), 120)
SET @최종작업시간 = CONVERT(VARCHAR(30), GETDATE(), 108)

CREATE TABLE #c_Batch_Process (EventType [NVARCHAR](30), Parameters INT, EventInfo [NVARCHAR](255))

SELECT   TOP 1
                @USER_SPID = [master].[dbo].[sysprocesses].spid,
                @USER_HOST = RTRIM([master].[dbo].[sysprocesses].hostname),
                @USER_PROGRAM = RTRIM([master].[dbo].[sysprocesses].program_name)
FROM        [master].[dbo].[sysprocesses]
WHERE     ([master].[dbo].[sysprocesses].open_tran = 1)
          OR ([master].[dbo].[sysprocesses].open_tran = 2)
          OR ([master].[dbo].[sysprocesses].open_tran = 3)
ORDER BY [master].[dbo].[sysprocesses].spid

IF @USER_SPID IS NOT NULL AND @USER_PROGRAM = 'PEIS'
   BEGIN
         SET @COMMAND_PROCESS = NULL
         SET @spid_1 = @USER_SPID
         SET @KiLL_PRO = 'KiLL ' + CONVERT(VARCHAR(4), @spid_1)

         EXEC(@KiLL_PRO)

         IF @@ERROR <> 0
            BEGIN
                     SET @DB_ERR = 444
                     RETURN
            END

         INSERT INTO #c_Batch_Process EXEC ('DBCC INPUTBUFFER(' + @spid_1 + ')')

         SELECT @COMMAND_PROCESS = EventInfo FROM #c_Batch_Process

         IF @COMMAND_PROCESS IS NULL
            BEGIN
                  SELECT @COMMAND_PROCESS = #c_Batch_Process.EventInfo

                  FROM #c_Batch_Process
            END

         IF @COMMAND_PROCESS IS NULL
            BEGIN
                  DELETE FROM #c_Batch_Process
                  INSERT INTO #c_Batch_Process EXEC ('DBCC INPUTBUFFER(' + @spid_1 + ')')
                  SELECT @COMMAND_PROCESS = #c_Batch_Process.EventInfo

                  FROM #c_Batch_Process
            END

         INSERT INTO [interflex3].[dbo].[C_Kill_Procedure]
                            ([USER_SPID],
                             [USER_HOST],
                             [USER_PROGRAM],
                             [USER_PROCEDURE],
                             [구분],
                             [최종작업일자],
                             [최종작업시간])
         VALUES
                            (@USER_SPID,
                             @USER_HOST,
                             @USER_PROGRAM ,
                             @COMMAND_PROCESS,
                             '1',
                             @최종작업일자,
                             @최종작업시간)
   END

GO

--SELECT * FROM [interflex3].[dbo].[C_Kill_Procedure]