存储过程实现历史表的数据记录

1.不成熟的方案 

　　1.对业务表的操作，已历史表的方式记录。

　　2.目前只支持单表 和 主从表   。不支持多表结构

　　

2.思路 之前项目是 针对存储过程开发的。 所以 业务表 加入 历史表 ，就得通过存储过程来实现 主要用到一些处理json 的函数。 参数是通过json  传输的。 

　　1.传入表结构 取到，用于复制表结构 ，构造 histroy 表.

　　2.根据插入的数据 来 数据表记录中是否存在 ，存在是更新，不存在则是插入。（操作行为）

 

3 第一步解析对于的数据

-- =============================================
-- Description: <Description,>
--调用
/*
DECLARE @jsonStr NVARCHAR(max),@jsonStr1 NVARCHAR(max),@value NVARCHAR(MAX);
SET @jsonStr1= N'{"Result":[{"Name":"WILL1","socre":"124","remark":"GWSort","ID":"2","Action":"update"},{"Name":"WILL1","socre":"124","remark":"GWSort","ID":"3","Action":"update"}],"ActionName":"MDM_SetGWTypeNSort","TName":"CS_Table","apiUrls":{"DeleteUrl":"/api/sn/Common/CommonSet","AddEditUrl":"/api/sn/Common/CommonSet"}}';
EXEC SP_SYS_Add_Resume @jsonStr1,'admin'
*/
-- =============================================
ALTER PROCEDURE [dbo].[SP_SYS_Resume]

@jsonStr NVARCHAR(MAX),@userCode NVARCHAR(50)
AS
BEGIN

DECLARE @TableName NVARCHAR(500),@Values NVARCHAR(MAX),@ValueType INT ,@JsonResult NVARCHAR(MAX)
,@DetailsJSON NVARCHAR(MAX),@ColoumnValue NVARCHAR(MAX),@countindex INT,@DetailsMainTableName NVARCHAR(500)

SET @DetailsJSON='';
SET @JsonResult='';
SET @TableName='';
SET @Values='';
SET @ValueType=0 ;
SET @ColoumnValue='[]';
SET @countindex =0;
SET @DetailsMainTableName='';
SET NOCOUNT ON;
--SET XACT_ABORT ON;--设置发生异常直接回滚

 

SELECT @TableName=Value
--FROM OPENJSON (@jsonStr) WHERE [Key]='MainTableName'--对应json 表名
FROM OPENJSON (@jsonStr) WHERE [Key]='MainTableName'--对应json 表名

SELECT @DetailsMainTableName=Value
FROM OPENJSON (@jsonStr) WHERE [Key]='DetailsMainTableName'--对应json 表名
-- 设置主从结构表

SELECT @DetailsJSON=Value,@Values=Value,@ValueType=Type FROM OPENJSON(@jsonStr) WHERE [Key] ='Result'
SELECT @ColoumnValue=Value FROM OPENJSON(@jsonStr) WHERE [Key] ='AddColumns'

IF ISNULL(@Values,'') <>''
BEGIN


EXEC dbo.SP_SYS_Add_Resume @Values,@ValueType,@TableName,@ColoumnValue
-- 是否有从表

--初始化参数
SET @Values='';
SET @ValueType=0 ;
SET @ColoumnValue='[]';
--注：表名中 State 不要重复出现

IF(@DetailsMainTableName<>'')
BEGIN
SET @TableName= @DetailsMainTableName -- 设置表名
END
ELSE
BEGIN
SET @TableName= REPLACE(@TableName,'State','Detail')
END


SELECT @countindex=COUNT(*) FROM OPENJSON(@DetailsJSON,'$.ResultDetails') WHERE [Key]='Resultdata'
IF(@countindex>0)
BEGIN
SELECT @Values=Value,@ValueType=Type FROM OPENJSON(@DetailsJSON,'$.ResultDetails') WHERE [Key]='Resultdata'
SELECT @ColoumnValue=Value FROM OPENJSON(@DetailsJSON,'$.ResultDetails') WHERE [Key] ='AddColumns'
END
ELSE
BEGIN
SELECT @Values=Value,@ValueType=Type FROM OPENJSON(@DetailsJSON) WHERE [Key]='ResultDetails'
-- SELECT @ColoumnValue=Value FROM OPENJSON(@DetailsJSON,'$.ResultDetails') WHERE [Key] ='AddColumns'
END

IF ISNULL(@Values,'')<>''
BEGIN
EXEC dbo.SP_SYS_Add_Resume @Values,@ValueType,@TableName,@ColoumnValue
END


END

 

END

第二部解析 插入数据

/*
Description:    [实现公用表新增]
1.解析出新增的表
2.如果是新增，查出新增的数据在插入， 否则 直接插入
3.是否存在多条记录 
--调用
 DECLARE @jsonStr NVARCHAR(max),@jsonStr1 NVARCHAR(max),@value NVARCHAR(MAX);
SET @jsonStr1= N'{"Result":[{"Name":"WILL1","socre":"124","remark":"GWSort","ID":"2","Action":"update"},{"Name":"WILL1","socre":"124","remark":"GWSort","ID":"3","Action":"update"}],"ActionName":"MDM_SetGWTypeNSort","TName":"CS_Table","apiUrls":{"DeleteUrl":"/api/sn/Common/CommonSet","AddEditUrl":"/api/sn/Common/CommonSet"}}';
EXEC SP_SYS_Add_Resume  @jsonStr1,'admin'
*/

ALTER  PROCEDURE [dbo].[SP_SYS_Add_Resume] 

      @Json NVARCHAR(MAX) -- josn 
     ,@ValueType INT -- josn 类型  --1 -string 2 num 3 bool  4  arry  5  object
     ,@TableName NVARCHAR(500) --表名
     ,@ColoumnValue NVARCHAR(MAX)
AS
BEGIN
     SET NOCOUNT ON;
     DECLARE @JsonResult NVARCHAR(max),@TableNameHistory NVARCHAR(500),@Sql NVARCHAR(max),
     @HisColoumnName NVARCHAR(MAX),@Action NVARCHAR(50),@Ids NVARCHAR(MAX),@error NVARCHAR(MAX),
     @altersql NVARCHAR(MAX),@sqlcolumn NVARCHAR(MAX),@sqlcolumns NVARCHAR(MAX),@sqlcolumnss NVARCHAR(MAX),@columnjson NVARCHAR(MAX),@HisColoumnNameNO NVARCHAR(MAX)

     SET @error='';
     SET @Ids ='' --ID 
     SET @Action ='';--行为
     SET @HisColoumnName =''; --列名
     SET @Sql='';
     SET @TableNameHistory='';
     SET @altersql =''; --增加列
     SET @sqlcolumn=''; -- 表结构增加列
     SET @sqlcolumns =''; -- 新增字段
     SET @sqlcolumnss=''; -- 别名 新增字段
     SET @columnjson =''; -- json构造字段
     SET @HisColoumnNameNO= '';
     --开始捕获异常
     BEGIN TRY  

        --解析Json中的表名

        SET @TableNameHistory=@TableName+'History';
    
        

        --检查表是否存在

        IF NOT EXISTS(select TOP 1 * from sys.objects where name = @TableName AND type='U')
        BEGIN
        SET @error ='没有'+@TableName
        RAISERROR(@error,16,1)
        END

        --解析表中列
        SELECT COLUMN_NAME   INTO #tablecoloumns   
        FROM information_schema.COLUMNS    WHERE TABLE_NAME=@TableNameHistory AND COLUMN_NAME <>'ID' 
         --COLLATE Chinese_PRC_CI_AS
        IF NOT EXISTS(select TOP 1 * from sys.objects where name = @TableNameHistory AND type='U')
        BEGIN
        --SET @error=@error +' 没有'+@TableNameHistory
        --RAISERROR('@error',16,1)

        SET @Sql=' SELECT *,0 AS SourceID,CAST('''' AS VARCHAR(30)) AS Event INTO '+@TableNameHistory+' FROM '+@TableName+' WHERE 1=0 '
        EXEC (@Sql);

        END


        IF (@ColoumnValue<>'')
        BEGIN
        
        -- 自动增加列是否需要实现
        --begin
        --对比之后需要新增的字段 
        SET @altersql ='ALTER TABLE '+ @TableNameHistory+'  ADD '
        SELECT @sqlcolumn= STUFF((
        SELECT ','+A.[Key]+CASE WHEN A.[Type]=1 THEN ' NVARCHAR(500)' WHEN A.[Type]=2 THEN ' INT' ELSE ' NVARCHAR(500)' END  FROM 
        (SELECT T.[Key],T.[Value],T.[Type]  FROM OPENJSON(@ColoumnValue) ROOT
         CROSS APPLY OPENJSON(ROOT.[Value]) t WHERE ROOT.[Key]=0 AND t.[Key]<>'ID') A
         LEFT JOIN #tablecoloumns B ON A.[Key]=B.COLUMN_NAME   COLLATE Chinese_PRC_CI_AS
         WHERE 1=1 AND ISNULL(b.COLUMN_NAME,'')=''
          FOR XML PATH('') ),1,1,'')
        
        IF(@sqlcolumn<>'')
         BEGIN
         SET @altersql+=@sqlcolumn
         EXEC (@altersql);
         END
        
         PRINT (@altersql);
        
        --新增列
        SELECT @sqlcolumns= STUFF((
        SELECT ','+A.[Key]  FROM 
        (SELECT T.[Key],T.Value,T.Type  FROM OPENJSON(@ColoumnValue) ROOT
         CROSS APPLY OPENJSON(ROOT.Value) t WHERE ROOT.[Key]=0 AND t.[Key]<>'ID') A
        -- LEFT JOIN #tablecoloumns B ON A.[Key]=B.COLUMN_NAME   COLLATE Chinese_PRC_CI_AS
    --     WHERE 1=1 AND ISNULL(b.COLUMN_NAME,'')=''
          FOR XML PATH('') ),1,0,'')

           --新增列 
              SELECT @sqlcolumnss= STUFF((
        SELECT ',C.'+A.[Key]  FROM 
        (SELECT T.[Key],T.Value,T.Type  FROM OPENJSON(@ColoumnValue) ROOT
         CROSS APPLY OPENJSON(ROOT.Value) t WHERE ROOT.[Key]=0 AND t.[Key]<>'ID') A
        -- LEFT JOIN #tablecoloumns B ON A.[Key]=B.COLUMN_NAME   COLLATE Chinese_PRC_CI_AS
        -- WHERE 1=1 AND ISNULL(b.COLUMN_NAME,'')=''
          FOR XML PATH('') ),1,0,'')


          --循环插入新增字段的数据源 

          
          SELECT @columnjson= STUFF((
        SELECT ','+A.[Key]+CASE WHEN A.[Type]=1 THEN ' NVARCHAR(500)  ' WHEN A.[Type]=2 THEN ' INT' ELSE ' NVARCHAR(500)' END +' ''$.'+A.[Key]+''''  FROM 
        (SELECT T.[Key],T.[Value],T.[Type]  FROM OPENJSON(@ColoumnValue) ROOT
         CROSS APPLY OPENJSON(ROOT.[Value]) t WHERE ROOT.[Key]=0 ) A
        -- LEFT JOIN #tablecoloumns B ON A.[Key]=B.COLUMN_NAME   COLLATE Chinese_PRC_CI_AS
        -- WHERE 1=1 AND ISNULL(b.COLUMN_NAME,'')=''
          FOR XML PATH('') ),1,1,'')

          END




           



        --找出表列名
        SELECT @HisColoumnName= STUFF((SELECT ',A.'+COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME <>'ID'  FOR XML PATH('')),1,1,'')
         SELECT @HisColoumnNameNO= STUFF((SELECT ','+COLUMN_NAME FROM  information_schema.COLUMNS WHERE TABLE_NAME=@TableName AND COLUMN_NAME <>'ID' FOR XML PATH('')),1,1,'')
        --4 批量   5 单个对象
        --遍历出列名 
        IF    @ValueType=4
        BEGIN

            --找出ID 
            SELECT   @ids=STUFF( (SELECT ','+TheValues.[value]
            FROM OPENJSON  (@Json) AS root
            CROSS APPLY OPENJSON ( root.value) AS TheValues WHERE TheValues.[key]='ID' FOR XML PATH('')),1,1,'') 

            --找出操作类型
            SELECT  @Action=TheValues.[value]
            FROM OPENJSON  (@Json) AS root
            CROSS APPLY OPENJSON ( root.value) AS TheValues WHERE root.[Key]=0
            AND  TheValues.[key]='Action'
            
            IF @Action='Insert'
            BEGIN
             --直接left join 找出插入ID ,插入history表
            
            SET @Sql= 'INSERT INTO ' +@TableNameHistory+' ('+@HisColoumnNameNO+',Event, SourceID'+' ) SELECT '+@HisColoumnName+','''+@Action+''' AS Event,A.ID '+' FROM '+@TableName +' AS A WHERE A.ID IN( ' 
            SET @Sql =@Sql+'SELECT A.ID FROM '+@TableName +' A LEFT JOIN '+@TableNameHistory+' B ON A.ID =B.SourceID WHERE ISNULL(B.ID,0)=0 ' +' )'
             
            END
            ELSE
            BEGIN

            --SET @Sql ='INSERT INTO ' +@TableNameHistory+' ('+@HisColoumnName+@sqlcolumns+', Event, SourceID'+' ) SELECT '+@HisColoumnName+','''+@Action+''' AS Event,ID '+' FROM '+@TableName +' WHERE ID IN( '+@Ids +' )'
            IF    @sqlcolumns<>''
            BEGIN
             

            SET @Sql ='INSERT INTO ' +@TableNameHistory+' ('+@HisColoumnNameNO+@sqlcolumns+', Event, SourceID'+' ) SELECT '+@HisColoumnName+@sqlcolumnss+','''+@Action+''' AS Event,A.ID '+' FROM '+@TableName +' AS A LEFT JOIN  ( SELECT * FROM OPENJSON('''+@ColoumnValue+''')WITH('+@columnjson+') ) C ON A.ID =C.ID   '+' WHERE A.ID IN( '+@Ids +' )'

            END
            ELSE
            BEGIN
            SET @Sql ='INSERT INTO ' +@TableNameHistory+' ('+@HisColoumnNameNO+', Event, SourceID'+' ) SELECT '+@HisColoumnName+','''+@Action+''' AS Event,A.ID '+' FROM '+@TableName +' AS A WHERE ID IN( '+@Ids +' )'
            END


            END
             
        END

        ELSE IF @ValueType=5
        BEGIN
 
            --找出ID 
     
            SELECT  @ids=root.[value]
            FROM OPENJSON  (@Json) AS root WHERE root.[Key]='ID'
            
            --找出操作类型
            SELECT  @Action=root.[value]
            FROM OPENJSON  (@Json) AS root WHERE root.[Key]='Action'
 
            IF @Action='Insert'
            BEGIN
             --直接left join 找出插入ID ,插入history表
            
            SET @Sql= 'INSERT INTO ' +@TableNameHistory+' ('+@HisColoumnNameNO+', Event, SourceID'+' ) SELECT '+@HisColoumnName+','''+@Action+''' AS Event,A.ID '+' FROM '+@TableName +' AS A WHERE A.ID IN( ' 
            SET @Sql =@Sql+'SELECT A.ID FROM '+@TableName +' A LEFT JOIN '+@TableNameHistory+' B ON A.ID =B.SourceID WHERE ISNULL(B.ID,0)=0 ' +' )'
             
            END
            ELSE
            BEGIN

        --    SET @Sql ='INSERT INTO ' +@TableNameHistory+' ('+@HisColoumnName+',Event,SourceID'+' ) SELECT '+@HisColoumnName+','''+@Action+''' AS Event,ID '+' FROM '+@TableName +' WHERE ID= '+@Ids

            IF    @sqlcolumns<>''
            BEGIN
            SET @Sql ='INSERT INTO ' +@TableNameHistory+' ('+@HisColoumnNameNO+@sqlcolumns+', Event, SourceID'+' ) SELECT '+@HisColoumnName+@sqlcolumnss+','''+@Action+''' AS Event,A.ID '+' FROM '+@TableName +' AS A LEFT JOIN  ( SELECT * FROM OPENJSON('''+@ColoumnValue+''')WITH('+@columnjson+') ) C ON A.ID =C.ID   '+' WHERE A.ID IN( '+@Ids +' )'
            END
            ELSE
            BEGIN
            SET @Sql ='INSERT INTO ' +@TableNameHistory+' ('+@HisColoumnNameNO+', Event, SourceID'+' ) SELECT '+@HisColoumnName+','''+@Action+''' AS Event,A.ID '+' FROM '+@TableName +' AS A  WHERE ID IN( '+@Ids +' )'
            END

            END
            

        END
         

         --直接


         DROP TABLE #tablecoloumns
        PRINT(@sql);
        BEGIN TRANSACTION
        EXEC (@sql);
        
      --结束捕获异常
      END TRY 
       --开始抛出异常
       BEGIN CATCH 
            IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

            DECLARE @ErrorMessage nvarchar(max) ,@ErrorNumber varchar(200),@ErrorSeverity varchar(200),@ErrorState varchar(200),
            @ErrorProcedure varchar(200),@ErrorLine varchar(200),@CreateDate datetime,@CreateBy varchar(20)
            SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorNumber = ERROR_NUMBER(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorProcedure = ERROR_PROCEDURE(),
            @ErrorLine = ERROR_LINE(),
            @CreateDate = getdate()
            --exec [dbo].[SYS_LogDBError] @ErrorMessage,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorProcedure,@ErrorLine,@Json,@CreateDate,@userCode

            SET @JsonResult=(select @ErrorMessage as 'ErrorMessage' for json PATH)
            SELECT @JsonResult AS JsonResult
        END CATCH  



    IF @@TRANCOUNT > 0
    BEGIN 
        COMMIT TRANSACTION;    --提交事务
        SET @JsonResult=(SELECT 'Success' as 'Result' for json PATH)
        SELECT @JsonResult AS JsonResult
    END

END

以上是 对表的操作 新增历史记录。