MSSQL 在执行DML，限制其影响数据的行数

aroc_lo

于 2023-07-14 09:32:22 发布

阅读量293

点赞数

文章标签： sqlserver sql 数据库

本文链接：https://blog.csdn.net/aroc_lo/article/details/131716313

版权

文章提出了在数据库操作中，通过创建存储过程来防止DELETE或UPDATE操作误删除或更新过多数据的问题。该存储过程在执行SQL语句前，会检查影响行数是否在预设的最小和最大范围内，不在范围内则不执行，并提供错误信息。这种方式强化了数据安全，避免依赖日志进行事后恢复。

摘要由CSDN通过智能技术生成

在实际的操作中，经常会发生以下的情况；

1、使用 DELETE 操作，条件没有设置好，删除的数据超过了预期的数据量；

2、使用 UPDATE 操作，也有类似 1 的情况；

虽然发生了预期外的数据操作，可以通过日志的方式还原，可避免损失，但是这并非什么好方法，这只是一种补救方法，这种方法明显不如一种预防方式；

由于水平有限，记录我自己想到的解决办法；

目的：执行某SQL语句时，少于设置的最小影响行数或多于设置的最大影响行数，都不执行；

方法说起来也超级简单，使用存储过程来执行该SQL语句。


CREATE PROCEDURE dbo.JoeSP_Pub_Run_SQL
(
	@SQL VARCHAR(2000),    -- SQL语句
	@MIN INT = 0,        -- 最小影响行数
	@MAX INT = 0        --最大影响行数
)
AS BEGIN
	SET NOCOUNT ON;

	BEGIN TRY
    	BEGIN TRANSACTION

			DECLARE @N INT,@SUCCESS INT,@F2 VARCHAR(2000)
			SET @SUCCESS =0
			SET @N = 1
			SET @F2 = ''

    		EXEC(@SQL)
			SET @N = @@rowcount
			IF @MIN>@N AND @MIN<>0
			BEGIN
				SET @SUCCESS=1
				SET @F2 ='当前影响 '+CAST(@N AS VARCHAR(10))+' 行， 没有达到设置的最小影响行数 '+CAST(@MIN AS VARCHAR(10))
			END
			IF @N>@MAX AND @MAX<>0
			BEGIN
				SET @SUCCESS=1
				SET @F2 ='当前影响 '+CAST(@N AS VARCHAR(10))+' 行， 超过了设置的最大影响行数 '+CAST(@MAX AS VARCHAR(10))
			END

			IF @SUCCESS=0
    			COMMIT TRANSACTION
			ELSE
				ROLLBACK TRANSACTION

			SELECT F1=@SUCCESS,F2=@F2
    END TRY
    BEGIN CATCH
		SELECT F1=1,F2=
    		'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
			',ErrorMessage '+ ERROR_MESSAGE() +
      		', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
      		', State ' + CONVERT(varchar(5), ERROR_STATE()) +
      		', Line ' + CONVERT(varchar(5), ERROR_LINE())
      
    	PRINT ERROR_MESSAGE();
      
      	IF XACT_STATE() <> 0 BEGIN
    		ROLLBACK TRANSACTION
      	END
    END CATCH;
END
GO