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';

 

MyCode:

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

 

Problem here is I must have to declare Table variable. How do i do it?