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;

score 1 · Accepted Answer · answered Jun 21 '18 at 00:55

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 '18 at 00:55

asmgx

7,328
15
82
143

Hi, Thank you for quick response. i even tried this EXEC('SELECT * FROM ' + @tablename) but says error at EXEC – Bokbob Jun 21 '18 at 01:13
You cant use EXEC with cursor, look at this https://stackoverflow.com/questions/1045880/using-a-cursor-with-dynamic-sql-in-a-stored-procedure – asmgx Jun 21 '18 at 01:16

Issue with accessing variable in Nested Cursors

1 Answers1