对没有动态sql的多个表运行相同的查询
对没有动态sql的多个表运行相同的查询
提问于 2015-08-25 17:17:16
我支持第三方软件包的SQL数据库。他们有很多他们所谓的“影子表”，实际上只是审计表。这一切都很好，但他们的系统并没有清理这些表，所以由我来做。他们还添加了新的“阴影表”，无需通知每次升级。我们清除表的旧方法是使用一长串DELETE FROM语句，但是这个列表已经变得非常长，很难维护。
为了使清除过程更容易维护，并自动捕获新的“阴影表”，我编写了以下存储过程。存储过程可以工作，但我更愿意找出一种不用使用游标和动态查询的方法，因为这将每天运行在许多不同的表上。在不使用游标和动态查询的情况下，是否有其他方法可以做到这一点？
DECLARE @workingTable varchar(128);
DECLARE @sqlText varchar(250);
DECLARE @CheckDate DATETIME = DATEADD(yy, -2, GETDATE());
DECLARE curKey SCROLL CURSOR FOR
SELECT name AS TableName
FROM dataTEST.sys.tables
WHERE (name like '%[_]h' OR name like '%[_]dh')
ORDER BY name
OPEN curKey
WHILE @@fetch_status = 0
BEGIN
FETCH NEXT FROM curKey INTO @workingTable
SET @sqlText = 'DELETE FROM DataTEST.dbo.' + @workingTable + ' WHERE LAST_MOD < ''' + CONVERT(CHAR(10), @CheckDate, 101) + ''';'
--PRINT @sqlText
EXEC (@sqlText)
END
CLOSE curKey
DEALLOCATE curKey回答 3
Stack Overflow用户
回答已采纳
发布于 2015-08-25 18:13:09
当您事先不知道表名时，我不知道如何避免使用动态SQL。Server有一个特性，您可以在select语句中对返回的每一行执行一次变量赋值。这可以用于消除游标，并将带所有delete语句的一个字符串传递给SQL server执行。
DECLARE @sqlText nvarchar(MAX) = ''; -- initialize because NULL + 'x' is NULL
DECLARE @CheckDate DATETIME = DATEADD(YEAR, -2, GETDATE());
SELECT @sqlText = @SqlText + 'DELETE FROM dataTEST.dbo.' + QUOTENAME(name)
+ ' WHERE LAST_MOD < @CheckDate ; '
FROM dataTEST.sys.tables
WHERE (name like '%[_]h' OR name like '%[_]dh')
ORDER BY name
IF @@ROWCOUNT > 0
EXEC sp_executesql @sqlText
, N'@CheckDate DATETIME'
, @CheckDate Stack Overflow用户
发布于 2015-08-25 17:21:46
我不认为在这里使用cursor和dynamic query是个坏主意
一种方法是附加delete查询，并在生成所有delete查询后在末尾执行它。
顺便说一句，游标只是用来构造动态查询的，所以没什么大不了的。
DECLARE @workingTable varchar(128);
DECLARE @sqlText nvarchar(max)='';
DECLARE @CheckDate DATETIME = DATEADD(yy, -2, GETDATE());
DECLARE curKey SCROLL CURSOR FOR
SELECT name AS TableName
FROM dataTEST.sys.tables
WHERE (name like '%[_]h' OR name like '%[_]dh')
ORDER BY name
OPEN curKey
WHILE @@fetch_status = 0
BEGIN
FETCH NEXT FROM curKey INTO @workingTable
SET @sqlText += 'DELETE FROM DataTEST.dbo.' + @workingTable + ' WHERE LAST_MOD < ''' + CONVERT(CHAR(10), @CheckDate, 101) + ''';'
END
CLOSE curKey
DEALLOCATE curKey
--PRINT @sqlText
EXEC (@sqlText)Stack Overflow用户
发布于 2015-08-25 17:37:27
通过执行以下操作，您可以获得更好的性能：
DECLARE @workingTable SYSNAME;
DECLARE @sqlText nvarchar(MAX);
DECLARE @CheckDate DATETIME = DATEADD(YEAR, -2, GETDATE());
DECLARE curKey CURSOR LOCAL FAST_FORWARD FOR
SELECT name AS TableName
FROM dataTEST.sys.tables
WHERE (name like '%[_]h' OR name like '%[_]dh')
ORDER BY name
OPEN curKey
WHILE @@fetch_status = 0
BEGIN
FETCH NEXT FROM curKey INTO @workingTable
SET @sqlText = 'DELETE FROM DataTEST.dbo.' + QUOTENAME(@workingTable)
+ ' WHERE LAST_MOD < @CheckDate'
Exec sp_executesql @sqlText
,N'@CheckDate DATETIME'
,@CheckDate
END
CLOSE curKey
DEALLOCATE curKey改进：
- 对sql服务器对象名称表(SYSNAME)使用适当的数据类型。
- 使用
sp_executesql而不是EXEC(@Sql) - 将参数传递为date，不要将其转换为字符串，以便sql server可以使用在该列上定义的索引。
- 使用
QUOTENAME()函数在表名周围放置方括号，以防表名中的任何一个表名是server中的保留关键字，因此查询不会出错。 - 使您的游标本地设置和fast_forward默认游标设置是全局的，您不需要这样做，对吗？
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32210193
复制相关文章
相似问题
腾讯云开发者
