Cursor with Dynamic SQL Query

Question

I'm trying to make a dynamic query, using a cursor, I wanna create filegroups to every tables on my database, I have this:

DECLARE @name VARCHAR(50)
DECLARE @query VARCHAR(50)

DECLARE vend_cursor CURSOR
    FOR SELECT name FROM sys.tables order by name asc

OPEN vend_cursor
FETCH NEXT FROM vend_cursor;  

WHILE @@FETCH_STATUS = 0   
BEGIN   

    PRINT 'FG_'+@name
    FETCH NEXT FROM vend_cursor INTO @name;  
END   

CLOSE vend_cursor   
DEALLOCATE vend_cursor

The print is because then I can see how the filegroup name will be, but I wanna add this: ALTER DATABASE AdventureWorks2012 ADD FILEGROUP FG_filegroupname

I know I have to use 'exec sys.sp_executesql', but how can I add this to my query? thanks in advance

Jeffrey · Accepted Answer · 2016-04-03 06:59:14Z

5

Yes you can do this with sp_executeSQL but the most important thing is that you need to setup a GLOBAL cursor, because sp_executeSQL is not in the same scope as the procedure which you are executing. See example

DECLARE @SQL nvarchar(1024), 
        @name varchar(255);

SET @SQL = 'DECLARE vend_cursor CURSOR GLOBAL
               FOR
               SELECT name FROM sys.tables order by name asc';

EXECUTE sp_executesql @SQL;
OPEN vend_cursor
FETCH NEXT FROM vend_cursor INTO @name;  

WHILE @@FETCH_STATUS = 0   
BEGIN   

    PRINT 'FG_'+@name
    FETCH NEXT FROM vend_cursor INTO @name;  
END   

CLOSE vend_cursor   
DEALLOCATE vend_cursor

edited Apr 3, 2016 at 6:59

answered Apr 3, 2016 at 0:56

Jeffrey

4122 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

Cursor with Dynamic SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related