T-SQL删除多个表错误子查询返回的值超过1个。

Question

我有下面的their来删除在tblCtrlTable中有它们的名称的表

Declare @Cmd nvarchar(max)

select @cmd = ( 
SELECT 'IF OBJECT_ID(''' +TABLE_NAME + ''') IS NOT NULL BEGIN DROP TABLE [' + TABLE_NAME + '] END;' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME IN (SELECT [TEST_TABLE] from tblCtrlTable WHERE FieldX=1 and FieldY = 'Pass'))

print @cmd

--EXECUTE sp_executesql @cmd

但是，我一直收到错误：

子查询返回的值超过一个。当子查询跟随=、!=、<、<=、>、>=或子查询用作表达式时，这是不允许的。

我哪里出问题了？

Answer 1

这里有一种你这样做的方法。它不能确保价值的顺序，但它是超级快速和容易。不太清楚为什么要检查object_id是否有效，though....it不能为NULL，因为它是一个表。而且您知道它是一个表，因为您正在查询INFORMATION_SCHEMA.TABLES. :)

Declare @Cmd nvarchar(max) = '' --Notice this is initially set to an empty string.

select @cmd = @cmd + 'IF OBJECT_ID(''' +TABLE_NAME + ''') IS NOT NULL BEGIN DROP TABLE [' + TABLE_NAME + '] END;' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME IN (SELECT [TEST_TABLE] from tblCtrlTable WHERE FieldX=1 and FieldY = 'Pass')

print @cmd

Answer 2

尝试表变量而不是变量：

Declare @Cmd TABLE (QUERY nvarchar(max))

INSERT INTO @Cmd(QUERY)
SELECT
            'IF OBJECT_ID(''' + TABLE_NAME + ''') IS NOT NULL BEGIN DROP TABLE [' + TABLE_NAME + '] END;'
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME IN (SELECT
                [TEST_TABLE]
            FROM tblCtrlTable
            WHERE FieldX = 1
            AND FieldY = 'Pass')


SELECT * FROM @cmd