Issue with accessing variable in Nested Cursors

Question

I am getting a set of table names from sys tables and trying to loop through them to get columns from them and then append to string in dynamic SQL.

I am having issues with syntax. Please help me. am trying to pass @tablename to the select * statement and it doesnt like it. Please help me

                DECLARE @col [VARCHAR](100);
            DECLARE @TableCursor AS CURSOR;


            SET 
            @TableCursor = CURSOR FOR
               SELECT   st.TABLE_NAME
               FROM     INFORMATION_SCHEMA.TABLES st
               WHERE    REPLACE(st.TABLE_NAME, 'TEST' + '_' + 'PIV' + '_', '') = 'Marketing_campaign'
                        AND st.TABLE_SCHEMA = 'DV';
            DECLARE @Tablename VARCHAR(100);

            OPEN @TableCursor;
            FETCH NEXT FROM @TableCursor 
                INTO @Tablename;

            WHILE @@FETCH_STATUS = 0
                BEGIN
                    DECLARE @colCursor AS CURSOR;

                    SET @colCursor = CURSOR FOR
                        SELECT * FROM  @Tablename; ****** this is where its complaining****

                    OPEN @colCursor;
                    FETCH NEXT FROM @colCursor 
                        INTO @col;
                    WHILE @@FETCH_STATUS = 0
                        BEGIN
                            CLOSE @colCursor;
                            DEALLOCATE @colCursor;

                        END;
                    FETCH NEXT FROM @TableCursor
                    INTO @Tablename;
                END;

            CLOSE @TableCursor;
            DEALLOCATE @TableCursor;

asmgx · Accepted Answer · 2018-06-21 00:55:19Z

1

You are selecting from @Tablename but in the definition of @Tablename it is varchar(100) You cant select from a varchar

if you want to select you need to select from table

look at this, it may help Table name as variable

answered Jun 21, 2018 at 0:55

asmgx

8,15019 gold badges102 silver badges186 bronze badges

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

2 Comments

Bokbob Over a year ago

Hi, Thank you for quick response. i even tried this EXEC('SELECT * FROM ' + @tablename) but says error at EXEC

asmgx Over a year ago

You cant use EXEC with cursor, look at this stackoverflow.com/questions/1045880/…

Collectives™ on Stack Overflow

Issue with accessing variable in Nested Cursors

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