This is one case where you have no choice but to use dynamic SQL. However, you should carefully validate the argument to avoid any possibility of a
SQL Injection[
^] vulnerability.
Something like this should work:
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