SqlServer：SqlServer（存储过程动态表查询（取消返回值），事务处理，批量还原sqlserver备份，强制删除被占用的数据库）

1.存储过程动态表查询

 

USE [NETWORKING_AUDIT]
GO
/****** Object:  StoredProcedure [dbo].[impConfigInfo]    Script Date: 01/04/2019 08:39:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        wm
-- Create date: 20181215
-- Description:    导入dbo.T_UNIT_AUDIT、dbo.T_UNIT_DELIVERY_CONFIG、dbo.T_ETL_CONFIG、dbo.T_AUDIT_TASK 配置信息
-- pzb 表字段：[市（县、区）] ,[行政区划代码],[单位层次],[单位编码],[单位名称]
--              ,[统一社会信用代码证号/_组织机构代码证号],[行业类型],[数据名称],[系统名称] ,[数据类型]
--              ,[是否含多套数据] ,[数据内容],[数据备份类型],[采集表类型],[是否市级部门集中管理],[备注]

-- =============================================
ALTER PROCEDURE [dbo].[impConfigInfo]
     @paramTableName varchar(200),
     @errorInfo  VARCHAR(8000) OUTPUT
AS
BEGIN
    
    ---开启事务
    BEGIN tran
    begin try  

    DECLARE @BakTableName VARCHAR(200)
    DECLARE @str VARCHAR(800)
    DECLARE @tableName VARCHAR(200)
    SET  NOCOUNT ON -----------------！！！注意设置，取消增删改返回值

    

    -----------------------------删除无效行
    DECLARE @delsql VARCHAR(8000)
    SET @delsql = '
    delete from '+@paramTableName+' 
    where ([行政区划代码] is null or [行政区划代码]=''null'' OR LEN([行政区划代码])=0) 
    and ([单位编码] is null or [单位编码]=''null'' OR LEN([单位编码])=0) 
    and ([单位名称] is null or [单位名称]=''null'' OR LEN([单位名称])=0);
    select distinct * into '+@paramTableName+'_1 from  '+@paramTableName+';
    drop table ['+@paramTableName+'];
    EXEC sp_rename '''+@paramTableName+'_1'','''+@paramTableName+''';
    '
    EXEC (@delsql);



    ------------------------------插入单位表信息前备份单位表
    
    SET @tableName = 'T_UNIT_AUDIT'
    --SELECT REPLACE(CONVERT(VARCHAR(200),GETDATE(),23),'-','')
    SET @BakTableName = @tableName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(200),GETDATE(),20),'-',''),' ',''),':','')+'_bak'    
    SET @str = '
    IF not EXISTS(
        SELECT 1 FROM [BackupsTables].sys.tables
        where name = '''+@BakTableName+'''
    )
    begin
    select * into [BackupsTables].[dbo].['+@BakTableName+'] from [NETWORKING_AUDIT].[dbo].['+@tableName+'];
    end
    '
    EXEC (@str)
    
    
    ------------------------------删除单位表重复信息
    DECLARE @sql1 VARCHAR(8000)
    SET @sql1 = '
    IF EXISTS(SELECT 1 
    FROM  dbo.T_UNIT_AUDIT a,'+@paramTableName+' b 
    WHERE a.XZ_CODE = b.[行政区划代码] 
    AND a.UNIT_CODE= b.[单位编码] 
    ) 
    BEGIN 
        DELETE FROM '+@paramTableName+' 
        WHERE [单位编码] IN 
        ( 
            SELECT b.[单位编码]  
            FROM  dbo.T_UNIT_AUDIT a,'+@paramTableName+' b 
            WHERE a.XZ_CODE = b.[行政区划代码] 
            AND a.UNIT_CODE= b.[单位编码] 
        ) 
        AND [行政区划代码] IN 
        ( 
        SELECT b.[行政区划代码]  
            FROM  dbo.T_UNIT_AUDIT a, '+@paramTableName+' b 
            WHERE a.XZ_CODE = b.[行政区划代码] 
            AND a.UNIT_CODE= b.[单位编码] 
        ) 
        END 
    '
    EXEC (@sql1);
    
    ---------插入单位表信息
    DECLARE @sql2 VARCHAR(8000)
    SET @sql2 = '
    INSERT INTO dbo.T_UNIT_AUDIT 
                ( XZ_CODE , 
                  UNIT_NAME , 
                  UNIT_CODE , 
                  INDUSTRY_CODE, 
                  INDUSTRY_NAME , 
                  SORT 
                ) 
         SELECT 
                distinct 
                [行政区划代码], 
                 [单位名称], 
                 [单位编码], 
                 (CASE WHEN [行业类型] LIKE ''%党政%'' THEN ''1009'' 
                  WHEN [行业类型] LIKE ''%医院%'' THEN ''6001'' 
                  WHEN [行业类型] LIKE ''%金融%'' THEN ''5001'' 
                  WHEN [行业类型] LIKE ''%国企%'' THEN ''4001'' 
                  WHEN [行业类型] LIKE ''%高校%'' THEN ''3001'' 
                  END),  
                  [行业类型], 
                  ''666666'' sort 
                  FROM '+@paramTableName+'          
    '
        
    EXEC (@sql2);
                 
                     
    ------------------------------插入配置表信息前备份配置表
    SET @tableName = 'T_UNIT_DELIVERY_CONFIG'
    SET @BakTableName = @tableName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(200),GETDATE(),20),'-',''),' ',''),':','')+'_bak'    
    SET @str = '
    IF not EXISTS(
        SELECT 1 FROM [BackupsTables].sys.tables
        where name = '''+@BakTableName+'''
    )
    begin
    select * into [BackupsTables].[dbo].['+@BakTableName+'] from [NETWORKING_AUDIT].[dbo].['+@tableName+'];
    end
    '
    EXEC (@str)
    
    
    ------------------------------删除配置表重复信息
    DECLARE @sql3 VARCHAR(8000)
    SET @sql3 = '
    IF EXISTS(SELECT 1 
    FROM  dbo.T_UNIT_DELIVERY_CONFIG a,'+@paramTableName+' b 
    WHERE a.XZ_CODE = b.[行政区划代码] 
    AND a.UNIT_CODE= b.[单位编码] 
    AND a.[FILE_NAME]=  b.[系统名称] 
    ) 
    BEGIN 
        DELETE FROM '+@paramTableName+' 
        WHERE [单位编码] IN 
        ( 
            SELECT b.[单位编码]  
            FROM  dbo.T_UNIT_DELIVERY_CONFIG a,'+@paramTableName+' b 
            WHERE a.XZ_CODE = b.[行政区划代码] 
            AND a.UNIT_CODE= b.[单位编码] 
            AND a.[FILE_NAME]=  b.[系统名称] 
        ) 
        AND [行政区划代码] IN 
        ( 
        SELECT b.[行政区划代码]  
            FROM  dbo.T_UNIT_DELIVERY_CONFIG a, '+@paramTableName+' b 
            WHERE a.XZ_CODE = b.[行政区划代码] 
            AND a.UNIT_CODE= b.[单位编码] 
            AND a.[FILE_NAME]=  b.[系统名称] 
        ) 
        END 
    '
  EXEC (@sql3);
  
  ---------插入配置表信息
  DECLARE @sql4 VARCHAR(8000)
    SET @sql4 = '
    INSERT INTO dbo.T_UNIT_DELIVERY_CONFIG 
                ( 
                  UNIT_CODE , 
                  UNIT_NAME , 
                  SYS_NAME , 
                  SYS_TYPE , 
                  COLLECT_METHOD , 
                  BACKUP_FILE_TYPE , 
                  ENCRYPT , 
                  COMPRESS , 
                  FILE_NAME , 
                  ACCOUNT_TYPE , 
                  XZ_CODE, 
                  INDUSTRY_CODE, 
                  INDUSTRY_NAME,
                  DB_BACKUP_MODE
                ) 
           SELECT 
           distinct 
           [单位编码], 
           [单位名称], 
           [系统名称], 
            (CASE  WHEN [数据类型] LIKE ''%财务%'' THEN ''0'' 
            WHEN [数据类型] LIKE ''%业务%'' THEN ''1'' 
            END) sys_type, 
             [采集表类型], 
           [数据备份类型], 
           1, 
           1, 
           [系统名称], 
           [数据类型], 
           [行政区划代码], 
                 (CASE WHEN [行业类型] LIKE ''%党政%'' THEN ''1009'' 
                  WHEN [行业类型] LIKE ''%医院%'' THEN ''6001'' 
                  WHEN [行业类型] LIKE ''%金融%'' THEN ''5001'' 
                  WHEN [行业类型] LIKE ''%国企%'' THEN ''4001'' 
                  WHEN [行业类型] LIKE ''%高校%'' THEN ''3001'' 
                  END), 
                   [行业类型],
                   ''全量'' 
                   FROM '+@paramTableName+'    
    '    
        EXEC (@sql4);
         
             
    ------------------------------插入ETL配置表信息前备份ETL配置表
    SET @tableName = 'T_ETL_CONFIG'
    SET @BakTableName = @tableName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(200),GETDATE(),20),'-',''),' ',''),':','')+'_bak'    
    SET @str = '
    IF not EXISTS(
        SELECT 1 FROM [BackupsTables].sys.tables
        where name = '''+@BakTableName+'''
    )
    begin
    select * into [BackupsTables].[dbo].['+@BakTableName+'] from [NETWORKING_AUDIT].[dbo].['+@tableName+'];
    end
    '
    EXEC (@str)
                            
    ------------------------------删除ETL配置表重复信息
    DECLARE @sql5 VARCHAR(8000)
    SET @sql5 = '
        IF EXISTS(SELECT 1 
    FROM  dbo.T_ETL_CONFIG a,'+@paramTableName+' b 
    WHERE a.XZ_CODE = b.[行政区划代码] 
    AND a.UNIT_CODE= b.[单位编码] 
    AND a.sys_name = b.[系统名称] 
    ) 
    BEGIN 
        DELETE FROM '+@paramTableName+' 
        WHERE [单位编码] IN 
        ( 
            SELECT b.[单位编码]  
            FROM  dbo.T_ETL_CONFIG a,'+@paramTableName+' b 
            WHERE a.XZ_CODE = b.[行政区划代码] 
            AND a.UNIT_CODE= b.[单位编码] 
            AND a.sys_name = b.[系统名称] 
        ) 
        AND [行政区划代码] IN 
        ( 
        SELECT b.[行政区划代码]  
            FROM  dbo.T_ETL_CONFIG a, '+@paramTableName+' b 
            WHERE a.XZ_CODE = b.[行政区划代码] 
            AND a.UNIT_CODE= b.[单位编码] 
            AND a.sys_name = b.[系统名称] 
        ) 
        END 
    '
    EXEC (@sql5);
    
    ---------插入ETL配置表信息
    DECLARE @sql6 VARCHAR(8000)
    SET @sql6 = '
    INSERT INTO dbo.T_ETL_CONFIG 
                ( UNIT_CODE , 
                  UNIT_NAME , 
                  SYS_NAME , 
                  TEMPLATE_NAME , 
                  XZ_CODE 
                ) 
        SELECT 
                distinct 
                a.[单位编码], 
               a.[单位名称], 
               a.[系统名称], 
               b.[对应转换模板路径], 
               a.[行政区划代码] 
               FROM '+@paramTableName+' a left join dbo.t_stcwpzb b 
               on a.[系统名称] = b.[财务软件及版本] 
               
    '
        
        EXEC (@sql6);
        
        ------------------------------插入审计任务表信息前备份审计任务表
    SET @tableName = 'T_AUDIT_TASK'
    SET @BakTableName = @tableName+'_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(200),GETDATE(),20),'-',''),' ',''),':','')+'_bak'    
    SET @str = '
    IF not EXISTS(
        SELECT 1 FROM [BackupsTables].sys.tables
        where name = '''+@BakTableName+'''
    )
    begin
    select * into [BackupsTables].[dbo].['+@BakTableName+'] from [NETWORKING_AUDIT].[dbo].['+@tableName+'];
    end
    '
    EXEC (@str)    
    
    ----------------------------删除审计任务表重复信息
    DECLARE @sql7 VARCHAR(8000)
    SET @sql7 = '
    IF EXISTS(SELECT 1 
    FROM  dbo.T_AUDIT_TASK a,'+@paramTableName+' b 
    WHERE a.XZ_CODE = b.[行政区划代码] 
    AND a.UNIT_CODE= b.[单位编码] 
    ) 
    BEGIN 
        DELETE FROM '+@paramTableName+' 
        WHERE [单位编码] IN 
        ( 
            SELECT b.[单位编码]  
            FROM  dbo.T_AUDIT_TASK a,'+@paramTableName+' b 
            WHERE a.XZ_CODE = b.[行政区划代码] 
            AND a.UNIT_CODE= b.[单位编码]          
        ) 
        AND [行政区划代码] IN 
        ( 
        SELECT b.[行政区划代码]  
            FROM  dbo.T_AUDIT_TASK a, '+@paramTableName+' b 
            WHERE a.XZ_CODE = b.[行政区划代码] 
            AND a.UNIT_CODE= b.[单位编码] 
        ) 
        END 
    '
    EXEC (@sql7);
    
    -----------插入审计任务表信息
    DECLARE @sql8 VARCHAR(8000)
    SET @sql8 = '
        INSERT INTO dbo.T_AUDIT_TASK
        ( UNIT_CODE ,UNIT_NAME ,
          INDUSTRY_CODE ,INDUSTRY_NAME ,
          YEAR ,DATA_TYPE ,
          STATE ,CREATE_TIME ,
          AUDIT_METHOD_ID ,IS_ENABLE ,
          STANDARD_DB_IP ,STANDARD_DB_NAME ,STANDARD_DB_TYPE ,
          XZ_CODE ,
          DOUBT_DB_IP ,DOUBT_DB_NAME ,DOUBT_DB_TYPE
        )
         SELECT distinct 
          a.[单位编码]
          ,a.[单位名称]
          ,(CASE WHEN [行业类型] LIKE ''%党政%'' THEN ''1009'' 
                  WHEN [行业类型] LIKE ''%医院%'' THEN ''6001'' 
                  WHEN [行业类型] LIKE ''%金融%'' THEN ''5001'' 
                  WHEN [行业类型] LIKE ''%国企%'' THEN ''4001'' 
                  WHEN [行业类型] LIKE ''%高校%'' THEN ''3001'' 
                  END), 
          a.[行业类型],2018,0,
          ''error'',GETDATE(),
          b.methodModelID,1,''100.80.10.36'',CAST(a.[行政区划代码] AS VARCHAR(200))+''_cw'',''sqlserver'',
           a.[行政区划代码],''100.80.10.30'',''410000_yd'',''sqlserver'' 
           FROM '+@paramTableName+' a CROSS JOIN [wqb_upgrade].dbo.auditMethodModel b 
             WHERE  b.DATA_TYPE = ''财务'' 
              AND b.INDUSTRY_CODE = ''1009''
    '
    EXEC (@sql8);
         
end try
begin catch
   SELECT @errorInfo =  Error_message() 
   if(@@trancount>0) 
      rollback tran  
  END catch
if(@@trancount>0)
commit tran 


END

 

2.事务处理

--常用语句就四个。

--Begin Transaction：标记事务开始。
--Commit Transaction：事务已经成功执行，数据已经处理妥当。
--Rollback Transaction：数据处理过程中出错，回滚到没有处理之前的数据状态，或回滚到事务内部的保存点。
--Save Transaction：事务内部设置的保存点，就是事务可以不全部回滚，只回滚到这里，保证事务内部不出错的前提下。

CREATE TABLE lives(Eat VARCHAR(200),Play VARCHAR(300),Numb int)

---开启事务
begin tran
--错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
begin try  
   --语句正确
   insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)
   --Numb为int类型，出错
   insert into lives (Eat,Play,Numb) values ('猪肉','足球','abc')
   --语句正确
   insert into lives (Eat,Play,Numb) values ('狗肉','篮球',2)
end try
begin catch
   select Error_number() as ErrorNumber,  --错误代码
          Error_severity() as ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到
          Error_state() as ErrorState ,  --错误状态码
          Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
          Error_line() as ErrorLine,  --发生错误的行号
          Error_message() as ErrorMessage  --错误的具体信息
   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
end catch
if(@@trancount>0)
commit tran  --如果成功Lives表中，将会有3条数据。

--表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型
select * from lives



--------调用带返回值的存储过程
DECLARE @zz VARCHAR(200)
EXEC ceshi @zz  output
SELECT @zz

 

 

3.批量还原sqlserver备份

 

 SET NOCOUNT ON
  DECLARE @disk NVARCHAR(max)
  DECLARE @folederPath NVARCHAR(max)
  DECLARE @cmdSql NVARCHAR(max)
  DECLARE @filePath NVARCHAR(max)
  DECLARE @targetDataPath NVARCHAR(max)
  DECLARE @targetLogPath NVARCHAR(max)
  DECLARE @restoreDate NVARCHAR(max)
  DECLARE @fileName NVARCHAR(max);
  
  --***********************需要配置************************
  ----@disk 存放sqlserver备份文件的盘符,例如：'d'
  SET @disk = 'e'
  ----@folederPath 存放sqlserver备份文件的文件夹路径,例如：'kk\test'
  SET @folederPath = 'Idea\qq'
  ----@targetPath 还原出来的数据文件存放的路径,例如：'D:\kk\ta\d'
  SET @targetDataPath = 'e:\Idea\qq'
  ----@targetPath 还原出来的日志文件存放的路径,例如：'D:\kk\ta\l'
  SET @targetLogPath = 'e:\Idea\qq'
  --*******************************************************
  
  
  SELECT @restoreDate = REPLACE(CONVERT(NVARCHAR(2000),GETDATE(),23),'-','')
  SET @cmdSql ='exec master..xp_cmdshell '''+@disk+':&&cd '+@folederPath+'&&dir /s/b'''
  
  CREATE TABLE #tempTable ( filePath NVARCHAR(max) );
  INSERT    #tempTable
            EXEC ( @cmdSql
                );
               
   
                
  DECLARE My_Cursor CURSOR
  FOR
    ( SELECT    filePath,right(filePath,CHARINDEX('\',reverse(filePath))-1) fileN
      FROM      #tempTable
    );        
  OPEN My_Cursor;
  FETCH NEXT FROM My_Cursor INTO @filePath,@fileName;
  WHILE @@FETCH_STATUS = 0
    BEGIN  
         
        IF @filePath != 'NUll'
            BEGIN
                    
                DECLARE @dataName NVARCHAR(max);
                DECLARE @logName NVARCHAR(max);
                DECLARE @restoreSql NVARCHAR(max);
               
                
                CREATE TABLE #tempTable2
                    (
                      LogicalName NVARCHAR(max) ,
                      a NVARCHAR(max) ,
                      b NVARCHAR(max) ,
                      c NVARCHAR(max) ,
                      d NVARCHAR(max) ,
                      e NVARCHAR(max) ,
                      f NVARCHAR(max) ,
                      g NVARCHAR(max) ,
                      h NVARCHAR(max) ,
                      i NVARCHAR(max) ,
                      j NVARCHAR(max) ,
                      k NVARCHAR(max) ,
                      l NVARCHAR(max) ,
                      m NVARCHAR(max) ,
                      n NVARCHAR(max) ,
                      o NVARCHAR(max) ,
                      p NVARCHAR(max) ,
                      q NVARCHAR(max) ,
                      r NVARCHAR(max) ,
                      s NVARCHAR(max) ,
                      t NVARCHAR(max)
                    );
                INSERT  #tempTable2
                        EXEC
                            ( 'RESTORE FILELISTONLY FROM DISK = '''
                              + @filePath + ''''
                            );
                SELECT  @dataName = LogicalName
                FROM    #tempTable2
                WHERE   LogicalName NOT LIKE '%log%';
                SELECT  @logName = LogicalName
                FROM    #tempTable2
                WHERE   LogicalName LIKE '%log%';
                SET @restoreSql = 'RESTORE DATABASE [' + @fileName + '_'
                    + @restoreDate + '] FROM DISK=''' + @filePath
                    + '''WITH MOVE ''' + @dataName + '''To '''
                    + @targetDataPath + '\' + @fileName + '.mdf'', 
                    MOVE ''' + @logName + ''' To''' + @targetLogPath + '\'
                    + @fileName + '.ldf'';
                    ';
                    
        
                exec (@restoreSql);
                PRINT '---------------------------------------分隔线-----------------------------------------';
              
                DROP TABLE #tempTable2;
            END;
        FETCH NEXT FROM My_Cursor INTO @filePath,@fileName;
    END;
  CLOSE My_Cursor; 
  DEALLOCATE My_Cursor;
  DROP TABLE #tempTable;

 

 

 

4.强制删除被占用的数据库

---------------查询数据库是否被占用
select * from master.sys.sysprocesses where dbid = db_id('kaifengshitiyuju078000000jindiekisxingzhengshiyezhuanbanaccess_20190212015105_5705723296216269111')


---------------方法1：强制断开连接
declare @d varchar(8000)   
set   @d= ' '   
select   @d=@d+ '   kill   '+cast(spid   as   varchar)+char(13)   
from master.sys.sysprocesses where dbid=db_id('lankaoxianchengshiguanliju036000000yongyouruanjiangrpu8sqlserver_20190130115932_8149424311300407836')   
exec(@d) 

---------------方法2：立即回滚事务设置脱机状态，然后还需要手动删除数据文件
ALTER DATABASE [410225_cw]
SET OFFLINE with rollback IMMEDIATE