子查询返回的值超过1。当子查询被用作表达

Question

我运行以下查询：

“子查询返回超过1个值。这是：

从表

但是得到错误搜索特定图案当子查询如下=，！=，不允许<，< =，>，> =，或当子查询用作表达“

DECLARE @SearchStr nvarchar(100) 
SET @SearchStr = '' 

--drop table #Results 

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) 
    SET NOCOUNT ON 
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) 
SET @TableName = '' 
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') 

WHILE @TableName IS NOT NULL 
BEGIN 

SET @ColumnName = '' 
SET @TableName = 'RAP1' 

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) 
BEGIN 
    SET @ColumnName = 
    (

     SELECT COLUMN_NAME 
     FROM  INFORMATION_SCHEMA.COLUMNS 
     WHERE   
     TABLE_NAME = PARSENAME(@TableName, 1) 
      AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal') 

    ) 

    IF @ColumnName IS NOT NULL 
    BEGIN 
     INSERT INTO #Results 
     EXEC 
     (
      'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + 
      ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 
     ) 
    END 
END 
END 
SELECT ColumnName, ColumnValue FROM #Results 

DROP TABLE #Results 

什么错误？如何获得结果？请帮忙。

Answer 1

这很可能是因为表RAP1有多个列，并且您试图将该表的每一列的值（名称）保存在一个变量中（在SET @ColumnName中）。

如果下面的查询返回超过1行那么这就是你的问题：

DECLARE @TableName nvarchar(256) 
SET @TableName = 'RAP1' 

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = PARSENAME(@TableName, 1) 
    AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal') 

我会改变你的脚本是这个样子：

DECLARE @SearchStr nvarchar(100) 
SET @SearchStr = '' 

--drop table #Results 

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) 
SET NOCOUNT ON 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @PrevColumnName nvarchar(128) 
SET @TableName = '' 
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') 

WHILE @TableName IS NOT NULL 
BEGIN 

SET @ColumnName = '' 
SET @PrevColumnName = '' 
SET @TableName = 'RAP1' 

IF @TableName IS NOT NULL 
BEGIN 
    WHILE (@ColumnName IS NOT NULL) 
    BEGIN 

     SELECT TOP 1 @ColumnName = COLUMN_NAME 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_NAME = PARSENAME(@TableName, 1) 
      AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal') 
      AND COLUMN_NAME > COALESCE(@PrevColumnName, @ColumnName) 

     IF @ColumnName = @PrevColumnName 
      begin 
       SET @ColumnName = NULL 
       SET @TableName = NULL 
      end 

     SET @PrevColumnName = @ColumnName 

     IF @ColumnName IS NOT NULL 
     BEGIN 
      INSERT INTO #Results 
      EXEC 
      (
       'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + 
       ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 
      ) 
     END 
     END 
    END 
END 
SELECT ColumnName, ColumnValue FROM #Results