TSQL – Simple Loop Through Result Set Using Cursor

ben himself's picture

This is the basic query text to use whenever you need to loop through a result set in SQL Server using TSQL. In this example we are getting every database name and printing it out.

/* Declare variable that will hold a single result */
DECLARE @DBName varchar(50)

/* Declare the cursor and “load” the query into it */
DECLARE c CURSOR For
SELECT name FROM sys.databases

/* Open the cursor */
OPEN c

/* Put the next (in this case first) result
from the cursor into the variable */
Fetch next FROM c into @DBName

/* Begin looping */
WHILE @@FETCH_STATUS=0
Begin
/* Do whatever you need to do with the data */
PRINT @DBName

/* IMPORTANT: Put the next result from the cursor into your
variable when you are done but before the loop ends */
FETCH NEXT FROM c into @DBName

End

/* Close and deallocate */
Close c
Deallocate c

Tags: 

Categories: