是否可以在storeprocedure中将模式名称和表名称作为参数传递？ [英] Is it possible to pass schema name and table name as parameter in storeprocedure?
问题描述
我正在尝试编写一个存储过程来删除大小的块中的大量（百万条记录）。由于在不同模式下有许多这样的表，而不是为每个表编写不同的存储过程。
例如：
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
推荐答案
这是一个你别无选择但只能使用动态SQL的情况。但是，您应该仔细验证参数，以避免出现任何可能的 SQL注入 [ ^ ]漏洞。
这样的事情应该有效：
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
- 添加where子句和调试模式
CREATE PROCEDURE spDeleteRecord
（
@SchemaTableName varchar（100），
@DeleteBatchSize int，
@DelayTime time，
@Whereclause varchar（1000），
@debug smallint = 1
）
AS
/ **************
示例：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
创建日期：05/17/2018
修改日期：
版本：1.0
**************** ** /
BEGIN
DECLARE @TableID int，@ ShermaName sysname，@ TableName sysname;
DECLARE @Query nvarchar（max），@ params nvarchar（max），@ DelayTimeValue char（8）;
SET 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'
DECLARE @DeleteRowCount int = 1;
WHILE（@DeleteRowCount> 0 ）
BEGIN
BEGIN TRANSACTION;
DELETE TOP（@DeleteBatchSize）'+ @SchemaName + N'。'+ @TableName +''+ @WhereClause + N';
--DELETE TOP（@DeleteBatchSize）'+ @SchemaName + N'。'+ @TableName +' '+ N';
SET @DeleteRowCount = @@ ROWCOUNT;
PRINT @DeleteRowCount;
COMMIT;
WAITFOR DELAY @DelayTimeValue;
END';
SET @params = N'@ DeleteBatchSize int ，@ DelaTimeValue char（8）';
SET @DelayTimeValue = Convert（char（8），@ DelayTime，108）;
如果@debug = 1
BEGIN
PRINT'exec sp_executesql N'''+ @Query +'''，N''' + @params +'''，'+'@ DeleteBatchSize ='+ convert（varchar（30），@ DeleteBatchSize）+'，'
+'@ DelayTimeValue ='''+ @DelayTimeValue + ''''
结束
ELSE
BEGIN
EXEC sp_executesql @查询，@ pa公羊，
@DeleteBatchSize = @DeleteBatchSize，
@DelayTimeValue = @DelayTimeValue;
END
结束
GO
--Add where clause and debug mode
CREATE PROCEDURE spDeleteRecord
(
@SchemaTableName varchar(100),
@DeleteBatchSize int,
@DelayTime time,
@Whereclause varchar(1000) ,
@debug smallint =1
)
AS
/**************
Example: 1.exec spDeleteRecord 'dbo.test',10,'00:00:10','where logtime < dateadd(yy,-1,getdate())',1
2.exec spDeleteRecord 'dbo.test',10,'00:00:10','where logtime < dateadd(yy,-1,getdate())',0
Create Date: 05/17/2018
Modified Date:
Version :1.0
******************/
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 + ' ' + @WhereClause + N';
--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);
IF @debug = 1
BEGIN
PRINT 'exec sp_executesql N''' + @Query + ''', N''' + @params + ''', '+ ' @DeleteBatchSize = ' + convert(varchar(30),@DeleteBatchSize) + ', '
+ ' @DelayTimeValue = ''' + @DelayTimeValue + ''''
END
ELSE
BEGIN
EXEC sp_executesql @Query, @params,
@DeleteBatchSize = @DeleteBatchSize,
@DelayTimeValue = @DelayTimeValue;
END
END
GO
这篇关于是否可以在storeprocedure中将模式名称和表名称作为参数传递？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
