How to use "USE ?" with CURSOR?

Question

I found a great example about how to use a cursor:

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200) 

DECLARE database_cursor CURSOR FOR 
    SELECT name 
    FROM MASTER.sys.sysdatabases 

OPEN database_cursor 

FETCH NEXT FROM database_cursor INTO @DB_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'
     EXEC sp_executesql @Command 

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END 

CLOSE database_cursor 
DEALLOCATE database_cursor 

The problem is that when I execute it it always returns the same filename and size. The article explains that it's because we have removed USE ?.

So let's put it back:

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200) 

DECLARE database_cursor CURSOR FOR 
    SELECT name 
    FROM MASTER.sys.sysdatabases 

OPEN database_cursor 

FETCH NEXT FROM database_cursor INTO @DB_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = ' USE ? SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'
     EXEC sp_executesql @Command 

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END 

CLOSE database_cursor 
DEALLOCATE database_cursor 

But SQL Server throws this error for every database:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'

Exactly 5 times which is the number of databases I have.

How to use this cursor?

Answer 1

You need to use the @DB_Name variable instead of a ?:

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200) 
DECLARE database_cursor CURSOR FOR 
SELECT name 
FROM MASTER.sys.sysdatabases 

OPEN database_cursor 

FETCH NEXT FROM database_cursor INTO @DB_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = ' USE ' + @DB_Name + ' SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'
     EXEC sp_executesql @Command 

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END 

CLOSE database_cursor 
DEALLOCATE database_cursor