Sqlserver 存储过程

1、功能描述: 有一个名叫OGG_EVENT的表，里面有个一段event_txt里面记录了ddl操作的文本，我们需要用一个存储过程来执行里面的ddl, 然后将EVENT_IMP设置为‘Y',表示已经执行了

OGG_EVNT表的定义

存储过程的概要

存储过程的内容:

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[execAllOGGDDL]    Script Date: 09/30/2015 14:45:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[execAllOGGDDL]


AS
BEGIN
  DECLARE @EVT_ID VARCHAR(10)
  DECLARE @EVT_ITM BIGINT


  DECLARE CUR CURSOR LOCAL FOR
     SELECT EVENT_ID, EVENT_ITM FROM OGG_EVENT WHERE EVENT_IMP='N' and EVENT_TXT <> 'END EVENT' order by EVENT_ID , EVENT_ITM


  --    打开游标
  OPEN CUR
   FETCH NEXT FROM CUR INTO @EVT_ID, @EVT_ITM
   WHILE @@FETCH_STATUS = 0
   BEGIN
     UPDATE OGG_EVENT SET EVENT_IMP='P' WHERE EVENT_ID=@EVT_ID AND EVENT_ITM=@EVT_ITM
     PRINT N'UPDATE OGG_EVENT SET EVENT_IMP=P WHERE EVENT_ID='+@EVT_ID +N'  EVENT_ITM=' +NCHAR(@EVT_ITM) +N'.' 
     EXEC execOGGDDL @EVT_ID, @EVT_ITM
     FETCH NEXT FROM CUR INTO @EVT_ID,@EVT_ITM
   END
 --    关闭游标
 CLOSE CUR
 --    释放游标
 DEALLOCATE CUR
END

GO

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[execOGGDDL]    Script Date: 09/30/2015 14:46:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[execOGGDDL] @evtId_ nvarchar(10), @evtItm_ bigint
AS BEGIN
BEGIN TRY
SET NOCOUNT ON
DECLARE @evtTxt nvarchar(max)
DECLARE @evtId nvarchar(10)
DECLARE @evtItm bigint
set @evtId = @evtId_
set @evtItm = @evtItm_


-- Test for any Events to process
IF (SELECT COUNT (*) FROM dbo.OGG_EVENT WHERE EVENT_ID = @evtId AND EVENT_ITM = @evtItm
AND UPPER(EVENT_IMP) = N'P') = 0
                        PRINT N'There are no Events to process for EventID= '+ @evtId +N'  EventITM='+NCHAR(@evtItm)+N'.'
-- Start looping through the records
                WHILE (SELECT COUNT (*) FROM dbo.OGG_EVENT WHERE EVENT_ID = @evtId AND EVENT_ITM=@evtItm
AND UPPER(EVENT_IMP) = N'P') > 0
                BEGIN
                -- Grab the first record out
                SELECT @evtTxt = EVENT_TXT FROM dbo.OGG_EVENT WHERE EVENT_ID =@evtId AND EVENT_ITM=@evtItm
AND EVENT_ITM = @evtItm AND UPPER(EVENT_IMP) = N'P'
                -- execute the statements
                IF @evtTxt != N'END EVENT'
                        exec(@evtTxt)
                -- Update the record to set the EVENT_IMP to 'Y'
                UPDATE dbo.OGG_EVENT SET EVENT_IMP = N'Y', EVENT_IMP_DATE =
GETDATE() WHERE EVENT_ID = @evtId AND EVENT_ITM = @evtItm
END END TRY
     BEGIN CATCH
                SELECT ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
     END CATCH
END

GO

特别说明:这不能完全说是我写的，开始是熊老师写的，然后我发现不能适合需求，在他的基础上改的 

执行存储过程dbo.execALLOGGDDL之前表dbo.ogg_event的内容

执行存储过程: exec dbo.execALLOGGDDL

执行完之后