SQL游标、存储过程及EXISTS 函数使用

USE [dqdata]
GO

/****** Object:  StoredProcedure [dbo].[dq_newadd_tableobject]    Script Date: 02/10/2020 16:38:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<dq_newadd_tableobject>
-- Create date: <2020-02-10>
-- Description:	 对 s_quicktablelist 增加新建立的表 
-- =============================================
CREATE PROCEDURE [dbo].[dq_newadd_tableobject] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

--7.1 把所有表名加入表名列表中


    declare @a int,@error int    
    declare @cname varchar(120), @ctype char(2), @dcrdate datetime ,@dmodidate datetime
    declare @nowdate char(10)
    SET @nowdate =  CONVERT(char(10), GETDATE(),120)
    set @a=1
    set @error=0
    --   begin tran  --申明事务
        
    --申明游标为cursor_objects
    declare cursor_objects cursor 
    for (
			select name,type,create_date,modify_date
			from sys.objects
			where type in ('U','V')
				 and (create_date >= @nowdate or modify_date >= @nowdate) 
--ORDER BY modify_date desc 
)

    --打开游标--
    open cursor_objects
    --开始循环游标变量--
    
    fetch next from cursor_objects into @cname,@ctype,@dcrdate,@dmodidate
    while @@FETCH_STATUS = 0    --返回被 FETCH语句执行的最后游标的状态--
        begin            
            print @cname
            
            IF not EXISTS (select 表名 from s_quicktablelist where 表名 like @cname)   
             begin         
				INSERT INTO s_quicktablelist
					   (表名, 表长名, 表说明, 表类型,建立时间,修改时间)
				VALUES (@cname,@cname,@cname,@ctype,@dcrdate,@dmodidate)
			end
--            set @error = @@ERROR
--            PRINT  'e1 ' + CONVERT(char(20),  @error)+ ' e2 ' + CONVERT(char(20),  @@ERROR)
			IF EXISTS (select 表名 from s_quicktablelist where 表名 like @cname)   
             begin  
				update s_quicktablelist
					set 建立时间 = @dcrdate
					,修改时间 = @dmodidate
				where 表名 = @cname
            end
            
            
            set @a=@a+1
            PRINT @a
            set @error= @error + @@ERROR   --记录每次运行sql后是否正确，0正确
            fetch next from cursor_objects into @cname,@ctype,@dcrdate,@dmodidate   --转到下一个游标
        end    
    
    
    close cursor_objects  --关闭游标
    deallocate cursor_objects   --释放游标


END

GO