Cursorless looping in T-SQL

cursorless-looping.sql

-- LET'S SAY WE NEED TO LIST THROUGH ALL LAST NAMES AND PERFORM SOME ACTION 
-- ON LAST NAMES BEGINNING WITH THE LETTER S
SELECT StaffId, LastName FROM Staff WHERE LastName LIKE 'S%'

-- THIS RETURNS A SET AS SUCH
-- StaffId LastName
-- ======= ========
-- 11  	   Sherwood
-- 35	   Schmidt
-- 41	   Sauvé
-- 52	   Sookhoo
-- 61      Stojkovich
-- 66      Sander

-- ONE MIGHT LOAD THIS DATASET INTO A CURSOR BY DOING THE FOLLOWING
DECLARE @StaffId int,
	@LastName nvarchar(50) 

DECLARE RecSet CURSOR FOR
SELECT StaffId, LastName FROM Staff WHERE LastName LIKE 'S%'

OPEN RecSet
FETCH NEXT FROM RecSet INTO @StaffId, @LastName

WHILE (@@FETCH_STATUS = 0)
BEGIN
	SELECT @LastName AS LastName
	FETCH NEXT FROM RecSet INTO @StaffId, @LastName
END

CLOSE RecSet
DEALLOCATE RecSet

-- CURSORS ARE VERY HEAVY AND NOT PERFORMANT, BECAUSE THEY TAKE UP MEMORY AND CREATE LOCKS
-- READ MORE: http://stackoverflow.com/a/58154/76302

-- HERE IS HOW TO DO THE SAME THING WITHOUT A CURSOR
DECLARE @Temp TABLE 
(
	Id int identity(1,1),
	StaffId int,
	LastName nvarchar(50)
)

INSERT INTO @Temp
SELECT StaffId, LastName FROM Staff WHERE LastName LIKE 'S%'

DECLARE @xMax INT
DECLARE @x INT = 1

SELECT @xMax = MAX(Id) FROM @Temp
WHILE @x < @xMax
BEGIN
	SELECT LastName FROM @Temp WHERE Id = @x	
	SET @x = @x + 1
END