问题描述
我正在尝试编写一个存储过程来删除大小的块中的大量（百万条记录）。由于在不同模式下有许多这样的表，而不是为每个表编写不同的存储过程。
例如：
spDeleteRecords'dbo.tblEmployee'，1000，'00：00：05';
我尝试过：
创建程序spDeleteRecord
@SchemaTableName varchar（100），
@DeleteBatchSize INT，
@DelayTime DATETIME
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @DeleteRowCount INT
SET @DeleteRowCount = 1
WHILE（@DeleteRowCount> 0）
BEGIN
BEGIN TRANSACTION
DELETE TOP（@DeleteBatchSize）@SchemaTableName;
SET @DeleteRowCount = @@ ROWCOUNT;
打印@DeleteRowCount;
COMMIT
等待延迟@DelayTime
结束
END
GO
I am trying to write a store procedure to delete huge(million of records) in chunk of small size. Since there are many such tables under different schema instead of writing different store procedure for each tables.
For example:
spDeleteRecords 'dbo.tblEmployee', 1000, '00:00:05';
What I have tried:
CREATE PROCEDURE spDeleteRecord
@SchemaTableName varchar(100),
@DeleteBatchSize INT,
@DelayTime DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeleteRowCount INT
SET @DeleteRowCount = 1
WHILE (@DeleteRowCount > 0)
BEGIN
BEGIN TRANSACTION
DELETE TOP(@DeleteBatchSize) @SchemaTableName;
SET @DeleteRowCount = @@ROWCOUNT;
PRINT @DeleteRowCount;
COMMIT
WAITFOR DELAY @DelayTime
END
END
GO
推荐答案
CREATE PROCEDURE spDeleteRecord
(
@SchemaTableName varchar(100),
@DeleteBatchSize int,
@DelayTime time
)
AS
BEGIN
DECLARE @TableID int, @SchemaName sysname, @TableName sysname;
DECLARE @Query nvarchar(max), @params nvarchar(max), @DelayTimeValue char(8);
SET NOCOUNT ON;
SET @TableID = OBJECT_ID(@SchemaTableName, 'U');
If @TableID Is Null RAISERROR('Table "%s" does not exist.', 16, 1, @SchemaTableName);
SET @SchemaName = QUOTENAME(OBJECT_SCHEMA_NAME(@TableID));
SET @TableName = QUOTENAME(OBJECT_NAME(@TableID));
SET @Query = N'
DECLARE @DeleteRowCount int = 1;
WHILE (@DeleteRowCount > 0)
BEGIN
BEGIN TRANSACTION;
DELETE TOP(@DeleteBatchSize) ' + @SchemaName + N'.' + @TableName + N';
SET @DeleteRowCount = @@ROWCOUNT;
PRINT @DeleteRowCount;
COMMIT;
WAITFOR DELAY @DelayTimeValue;
END';
SET @params = N'@DeleteBatchSize int, @DelayTimeValue char(8)';
SET @DelayTimeValue = Convert(char(8), @DelayTime, 108);
EXEC sp_executesql @Query, @params,
@DeleteBatchSize = @DeleteBatchSize,
@DelayTimeValue = @DelayTimeValue
;
END
GO
这篇关于是否可以在storeprocedure中将模式名称和表名称作为参数传递？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！