s23user Ответов: 2

Можно ли передать имя схемы и имя таблицы в качестве параметра в storeprocedure?

Я пытаюсь написать процедуру хранения, чтобы удалить огромный(миллион записей) кусок небольшого размера. Поскольку существует много таких таблиц по разным схемам, вместо того чтобы писать разные процедуры хранения для каждой таблицы.
Например:
spDeleteRecords 'dbo.tblEmployee', 1000, '00:00:05';

Что я уже пробовал:

Создать процедуру spDeleteRecord
@SchemaTableName varchar(100),
@DeleteBatchSize INT,
@DelayTime DATETIME
АС
НАЧАТЬ
УСТАНОВИТЕ NOCOUNT ON;
Объявить @DeleteRowCount INT
SET @DeleteRowCount = 1

WHILE (@DeleteRowCount > 0)
НАЧАТЬ
НАЧАТЬ ТРАНЗАКЦИЮ
Удалить TOP(@DeleteBatchSize) @SchemaTableName;
SET @DeleteRowCount = @@ROWCOUNT;
PRINT @DeleteRowCount;
СОВЕРШИТЬ
WAITFOR DELAY @DelayTime
КОНЕЦ
КОНЕЦ
ГО

2 Ответов

Рейтинг:
16

Richard Deeming

Это один из случаев, когда у вас нет другого выбора, кроме как использовать динамический SQL. Однако вы должны тщательно проверить этот аргумент, чтобы избежать любой возможности SQL-инъекция[^] уязвимость.

Что-то вроде этого должно сработать:

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

Рейтинг:
0

Member 10628421

--Добавить предложение where и режим отладки
Создать процедуру spDeleteRecord
(
@SchemaTableName varchar(100),
@DeleteBatchSize инт,
@DelayTime time,
@Whereclause varchar(1000) ,
@debug smallint =1
)
АС
/**************

Пример: 1.exec spDeleteRecord 'dbo.test',10,'00:00:10','где logtime < dateadd(yy,-1,getdate())',1
2.exec spDeleteRecord 'dbo.test',10,'00:00:10','где logtime < dateadd(yy,-1,getdate())',0

Дата Создания: 17.05.2018

дата изменения:

Версия :1.0
******************/
НАЧАТЬ
Объявите @TableID int, @SchemaName sysname, @TableName sysname;
Объявите @Query nvarchar(max), @params nvarchar(max), @DelayTimeValue char(8);

УСТАНОВИТЕ NOCOUNT ON;

SET @TableID = OBJECT_ID(@SchemaTableName, 'U');
Если @TableID имеет значение Null RAISERROR('таблица "%s" не существует.', 16, 1, @SchemaTableName);

SET @SchemaName = QUOTENAME(OBJECT_SCHEMA_NAME(@TableID));
SET @TableName = QUOTENAME(OBJECT_NAME(@TableID));

SET @Query = N'
Объявить @DeleteRowCount int = 1;
WHILE (@DeleteRowCount > 0)
НАЧАТЬ
НАЧАТЬ ТРАНЗАКЦИЮ;
Удалить TOP(@DeleteBatchSize) '+ @SchemaName + N'.' + @TableName + ''+ @WhereClause + N';
--Удаление верхней(@DeleteBatchSize) '+ @Имясхемы + Н'.' + @Имятаблицы + ''+ Н';
SET @DeleteRowCount = @@ROWCOUNT;
PRINT @DeleteRowCount;
СОВЕРШИТЬ;

WAITFOR DELAY @DelayTimeValue;
Конец';

SET @params = N'@DeleteBatchSize int, @DelayTimeValue char(8)';
SET @DelayTimeValue = Convert(char(8), @DelayTime, 108);

Если @debug = 1
НАЧАТЬ

Принт 'exec для процедуры sp_executesql Н"' + @запрос + "', Н"' + аргумент @params + "', '+ '@DeleteBatchSize = ' + конвертировать(тип varchar(30),@DeleteBatchSize) + ', '
+ ' @DelayTimeValue = "' + @DelayTimeValue + ""

КОНЕЦ
ЕЩЕ
НАЧАТЬ
EXEC sp_executesql @Query, @params,
@DeleteBatchSize = @DeleteBatchSize,
@DelayTimeValue = @DelayTimeValue;
КОНЕЦ




КОНЕЦ
ГО

Richard Deeming

Поздравляю - вы только что заново ввели SQL-инъекция[^] уязвимость, которую мы пытались избежать, и уничтожили вашу базу данных!

НИКОГДА используйте конкатенацию строк для построения SQL-запроса. ВСЕГДА используйте параметризованный запрос.