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 - 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? — underscore_d
– underscore_d, Commented Oct 30, 2017 at 14:34

Stanislav Kundii · Accepted Answer · 2017-10-30 14:59:47Z

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, 2017 at 14:59

answered Oct 30, 2017 at 14:33

Stanislav Kundii

2,9041 gold badge15 silver badges19 bronze badges

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

2 Comments

underscore_d Over a year ago

An answer should also explain why it's changing what it's changing.

Geesh_SO Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related