SQL Server : EXECUTE error when trying to query with variable statements

Question

The task is to scroll through all tables and columns to find the table and column names containig the searched value. The script I use is:

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;

When this is executed, I get an error:

Incorrect syntax near the keyword 'EXEC'

Please help me make this code work

Possible duplicate of [How do I find a value anywhere in a SQL Server Database?](https://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database) — SMor, Feb 02 '18 at 13:02
You are not ready for this level of complexity. And generally speaking, most any question/task you have has already been addressed so searching the internet should be your first step. This specific task is one of those - [search all columns all tables](https://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database) — SMor, Feb 02 '18 at 13:04
Sure i'm not Smor, otherwise why would i ask for help... Anyway thanks for your reply — Eugene, Feb 05 '18 at 09:39

score 1 · Accepted Answer · answered Feb 02 '18 at 11:28

There are a few problems with this line...

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

First, make sure that you have spaces in the necessary places in your sql string...

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

Next, if you execute a SELECT statement, the results are a data-set, not a scalar. This also means that the data-set is also not returned in the same as a function returns a scalar result.

It is possible to use EXEC @myReturn = spSomethingOrAnother(@param, @anotherParam); to capture anything sent back with a RETURN statement (At the end of the SP), but I don't think that works with Dynamic SQL...

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

Your next option could be to create a table to insert the results in to, then check that table...

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)

...

Thanks, Mat! will try to make use of your recommendations! – Eugene Feb 05 '18 at 09:38 — Eugene, Feb 05 '18 at 09:38
Thanks again, with your hint on INSERTing got it working – Eugene Feb 05 '18 at 15:18 — Eugene, Feb 05 '18 at 15:18

Eugene · Answer 2 · 2018-02-06T07:08:45.107

{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}

SQL Server : EXECUTE error when trying to query with variable statements

2 Answers2