Having difficulty accessing variable from outer cursor in SQL Server (Transact)

Question

Below is my code, it doesn't do much at the moment but my ultimate goal is to look at every text-like field in the database, checking it for a certain condition and modifying it if necessary.

I've been building towards this and everything was going okay until I actually tried to retrieve the value in the column.

The problem occurs when I try to set @CurrentValue. The top 1 code is just temporary until I later extend the code but this is not the problem. In testing this I discovered that it errors when trying to make use of @TableName from the inner while loop.

Must declare the table variable "@TableName".

I'm not an expert with procedural Transact SQL. Is there a way I can access this variable from this location?

--Edit following config lines as necessary
USE mytable
DECLARE @SchemaName SYSNAME = 'dbo'
DECLARE @TableName SYSNAME

DECLARE TableCursor CURSOR FOR
SELECT t.name 
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = @SchemaName
  and lower(t.name) like 'online%';

OPEN TableCursor

FETCH NEXT FROM TableCursor
INTO @TableName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        PRINT '-----'
        PRINT @TableName
        PRINT '-----'

        DECLARE MatchingColumns CURSOR LOCAL FOR
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE 
        TABLE_NAME = @TableName
        AND
        DATA_TYPE in ('nvarchar', 'varchar', 'ntext', 'text', 'nchar', 'char');

        DECLARE @ColumnName NVARCHAR(MAX)

        OPEN MatchingColumns

        FETCH NEXT FROM MatchingColumns
        INTO @ColumnName

        WHILE @@FETCH_STATUS = 0
        BEGIN

            DECLARE @CurrentValue NVARCHAR(MAX)

            set @CurrentValue = (
                select top 1 @ColumnName
                from @TableName
            )


            PRINT @ColumnName

            FETCH NEXT FROM MatchingColumns
            INTO @ColumnName

        END

        CLOSE MatchingColumns
        DEALLOCATE MatchingColumns

        FETCH NEXT FROM TableCursor
        INTO @TableName


    END

CLOSE TableCursor
DEALLOCATE TableCursor

Possible duplicate of [Table name as variable](https://stackoverflow.com/questions/2838490/table-name-as-variable) - in fact, too late for me to flag, but I like this one better because it warns about quoting: [How do I use SQL Server table name in select query with a variable?](https://stackoverflow.com/questions/10521144/how-do-i-use-sql-server-table-name-in-select-query-with-a-variable) — underscore_d, Oct 30 '17 at 14:34

Stanislav Kundii · Accepted Answer · 2017-10-30T14:59:47.970

0

Replace

set @CurrentValue = (
                select top 1 @ColumnName
                from @TableName
            )

ON

declare @cmd nvarchar(200)
set @cmd  = N'SELECT TOP 1  @CurrentValue=' +  @ColumnName + ' from ' + @TableName
EXECUTE @CurrentValue = sp_executesql @cmd, N'@CurrentValue VARCHAR(MAX)',  @CurrentValue OUT

edited Oct 30 '17 at 14:59

answered Oct 30 '17 at 14:33

Stanislav Kundii

2,874
1
11
17

An answer should also explain _why_ it's changing what it's changing. – underscore_d Oct 30 '17 at 14:47
I tried to do this and it simply returned a number. When I debug it I can see it has produced the correct SQL `SELECT top 1 MyColumn from MyTable varchar` and nothing crashes, but `CurrentValue` has a value of `214` instead of the text value. – Geesh_SO Oct 30 '17 at 14:54

Having difficulty accessing variable from outer cursor in SQL Server (Transact)

1 Answers1