代码之家 › 专栏 › 技术社区 › Eugene

SQL Server:尝试使用变量语句查询时出错

exec tsql sql-server

Eugene · 技术社区 · 6 年前

任务是滚动所有表和列,以找到包含搜索值的表和列名。我使用的脚本是:

IF Object_id('tempdb..#temp_sar') IS NOT NULL 
  DROP TABLE #temp_sar 
go 

CREATE TABLE #temp_table 
( 
     [table_name]  VARCHAR, 
     [column_name] VARCHAR 
) 

DECLARE @Table_Name VARCHAR 
DECLARE @Column_Name VARCHAR 
DECLARE @Search_Value UNIQUEIDENTIFIER = CONVERT(UNIQUEIDENTIFIER, '303D9191-E201-4299-809E-FC7B0213F73C') 

DECLARE @CURSOR CURSOR 

SET @CURSOR = CURSOR scroll  FOR 
     (SELECT s.table_name, 
            s.column_name 
      FROM information_schema.columns s 
      WHERE s.data_type = 'uniqueidentifier') 

OPEN @CURSOR 

FETCH next FROM @CURSOR INTO @Table_Name, @Column_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    IF @Search_Value = EXEC ('select distinct' + @Column_Name + 'from' 
                             + @Table_Name + 'where' + @Column_Name + '=' 
                              + @Search_Value) 
        INSERT INTO #temp_table ([table_name], [column_name]) 
        VALUES (@Table_Name, @Column_Name) 

    FETCH next FROM @CURSOR INTO @Table_Name, @Column_Name 
END 

CLOSE @CURSOR 

SELECT * FROM #temp_table;

执行此操作时,我得到一个错误:

关键字“EXEC”附近的语法不正确

请帮助我使此代码正常工作

2 回复 | 直到 6 年前

MatBailie 6 年前

这条线有一些问题。。。

  IF @Search_Value = Exec('select distinct' + @Column_Name + 'from' 
                          + @Table_Name + 'where' + @Column_Name + '=' 
                          + @Search_Value)

首先,确保sql字符串中必要的位置有空格。。。

Exec('select distinct ' + @Column_Name + ' from ' 
                        + @Table_Name + ' where ' + @Column_Name + ' = ' 
                        + @Search_Value
)

接下来,如果执行 SELECT 语句,则结果是一个数据集,而不是标量。这也意味着数据集的返回方式与函数返回标量结果的方式不同。

可以使用 EXEC @myReturn = spSomethingOrAnother(@param, @anotherParam); 用 RETURN 陈述 (SP末尾) ,但我认为这不适用于动态SQL。。。

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql

下一个选项可能是创建一个表,将结果插入其中,然后检查该表。。。

INSERT INTO @TABLE EXEC @query with SQL Server 2000

CREATE TABLE #result (search_value UNIQUEIDENTIFIER)

INSERT INTO 
  #result (
    search_value
  )
EXEC(
  'select distinct ' + @Column_Name + ' from ' 
                     + @Table_Name + ' where ' + @Column_Name + ' = ' 
                     + @Search_Value
)

IF EXISTS (SELECT * FROM #result WHERE search_value = @search_value)

...

Eugene 6 年前

{USE PROD
GO
IF OBJECT_ID('tempdb..#source_table') IS NOT NULL DROP TABLE #source_table 
GO
IF OBJECT_ID('tempdb..#result_table') IS NOT NULL DROP TABLE #result_table 
GO
CREATE TABLE #result_table (Table_Name nvarchar(max),Column_Name nvarchar(max),Searched_Value uniqueidentifier)
DECLARE @CURSOR             CURSOR
DECLARE @Table_Name         nvarchar(max)
DECLARE @Column_Name        nvarchar(max)
DECLARE @Search_Value       nvarchar(max) = concat('''','ABBDFFEA-4576-4AA9-854E-A016433C54F0','''')
SET @CURSOR  = CURSOR SCROLL
    FOR
    (
        select s.TABLE_NAME, s.COLUMN_NAME 
        from INFORMATION_SCHEMA.COLUMNS s
        where s.DATA_TYPE = 'uniqueidentifier'
    )
OPEN @CURSOR
FETCH NEXT FROM @CURSOR INTO @Table_Name, @Column_Name
WHILE @@FETCH_STATUS = 0  
BEGIN
INSERT INTO #result_table (Table_Name, Column_Name, Searched_Value) EXEC('select distinct ' + ''''+ @Table_Name + '''' + ' AS Table_Name , ' + ''''+ @Column_Name + '''' + ' AS Column_Name , ' + '[' + @Column_Name + ']' + ' from ' + '[' + @Table_Name + ']' + ' where ' + '[' + @Column_Name + ']' + ' = ' + @Search_Value)
FETCH NEXT FROM @CURSOR INTO @Table_Name, @Column_Name
END
CLOSE @CURSOR
--results
SELECT * FROM #result_table}