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? — SMor
– SMor, Commented Feb 2, 2018 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 — SMor
– SMor, Commented Feb 2, 2018 at 13:04
Sure i'm not Smor, otherwise why would i ask for help... Anyway thanks for your reply — Eugene
– Eugene, Commented Feb 5, 2018 at 9:39

MatBailie · Accepted Answer · 2018-02-02 11:28:41Z

1

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)

...

answered Feb 2, 2018 at 11:28

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eugene Over a year ago

Thanks, Mat! will try to make use of your recommendations!

Eugene Over a year ago

Thanks again, with your hint on INSERTing got it working

Eugene · Accepted Answer · 2018-02-06 07:08:45Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related