How to use "USE ?" with CURSOR?

October 7, 2022

I found a great example about how to use 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 the computer says:

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

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

How to use CURSOR?

>Solution :

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