How do you do loop through records one at a time in SQL server?

How do you do loop through records one at a time in SQL server?

This task is quite elementary when it comes to regular programming, however this can be challenging when we are talking about programming in SQL server. The reason for this is that Transact SQL is best at fetching recordsets altogether and not one record at a time. It is optimized for performance and hence is the happiest when working with a bunch of records.

Nevertheless we need to be able to loop through records one at a time. You may want to get customer id from a CUSTOMER table, then use this primary key to pull related data from the Orders table. One way you can do this is using CURSORS which will offer you the ability to loop through records one at a time. Cursors should never be your first choice as they can add a performance hit when it comes to database tuning. Instead you could use while loop or temp tables to do the same action.
Regardless for this blog post I am including an example of a CURSOR that will loop through all the databases and then switch RECOVERY mode to SIMPLE, one by one.

---

--declaration of variables

declare
@dbnm sysname,
@sql varchar(100)


-- begin cursor to get the database names

declare cursor_db cursor
for select name from sys.databases where name != 'tempdb'

-- using a cursor to loop through database names and change recovery model

open cursor_db
fetch next from cursor_db into @dbnm

while @@fetch_status = 0

begin

--print 'database is ' + @dbnm

set @sql='alter database ' + @dbnm + ' set recovery simple'
print 'sql is ' + @sql
exec (@sql)


fetch next from cursor_db into @dbnm
end

close cursor_db
deallocate cursor_db