In MS SQL Server, there are multiple ways to loop through query results. I will show you different ways to loop through a list of database names.

Use cursor

The code below will loop through the results of SELECT sys.databases.database_id, sys.databases.name FROM sys.databases query.

DECLARE @cursor_position CURSOR
DECLARE @db_id INT            -- Need it to hold value of database_id.
DECLARE @dbname NVARCHAR(100) -- Need it to hold value of name.
 
-- Set the cursor position from a SQL query.
SET @cursor_position = CURSOR FOR
    SELECT sys.databases.database_id, sys.databases.name FROM sys.databases
 
-- Fetch the 1st cursor position.
OPEN @cursor_position
FETCH NEXT
    FROM @cursor_position INTO @db_id, @dbname
 
-- Loop through the cursor.
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @db_id AS ID, @dbname AS "DB NAME"
 
    -- Fetch the next cursor position.
    FETCH NEXT
        FROM @cursor_position INTO @db_id, @dbname
END
 
-- Clean up.
CLOSE @cursor_position
DEALLOCATE @cursor_position

Output

MS SQL Server

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.

MS SQL - Loop through query results

Use cursor

About the author