SQL and Loops: The cursor and while

Purpose
The purpose of this tutorial is to show the operation of a cursor and a while loop along with a stored procedure.

It is rare that I see a cursor in action nowadays typically with a data source that does not have a unique identity column that you can reliably iterate through...or in cases where ROW_NUMBER() is not used which can attach a sequential identity column to rows of data. Usually I see the use of while loops that have a source (like a temporary table) with a unique identity column (like an auto-incrementing primary key).

The ROW_NUMBER():
Creating a unique auto-incrementing row number for each row.

	SELECT 
		ROW_NUMBER() OVER (
			ORDER BY peopleNames
		) rNumber
		, peopleNames
	FROM 
		someTable;

The CURSOR:
The cursor can iterate through rows of data without the need of a unique identity column that could be used to reliably step through rows of data. But, there is more overhead to manage.

	DECLARE @compilation VARCHAR(MAX); -- Store the data through iterations
	DECLARE @currentValue VARCHAR(100); -- At each iteration of the loop, the current value goes here
	DECLARE cursorName CURSOR FOR
		SELECT peopleNames FROM someTable; -- Data to iterate through

	OPEN cursorName;
	FETCH NEXT FROM cursorName INTO @currentValue; -- Load the first iteration of data
	WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @compilation = CONCAT(@compilation, ',', @currentValue); -- Save the data
			FETCH NEXT FROM cursorName INTO @currentValue; -- Load the next iteration of data
		END;
	CLOSE cursorName;
	DEALLOCATE cursorName;

	SELECT @compilation; -- Return the results

The WHILE:
This is what I see most of the time, perhaps because it is common in many languages. The only drawback is that you need to be able to exit the loop condition and that commonly requires the data source has a unique auto-incrementing identity column (like a primary key). And, like a cursor, there is more overhead to manage.
I will mention that, in this particular example, you could use STRING_AGG (which concats with a separator) the peopleNames together with a single SELECT statement thereby negating the need for a WHILE...but that is only available in newer SQL versions.

	CREATE TABLE #someTable
	(
	rNumber INT NOT NULL IDENTITY(1, 1), -- Does not need to be a primary key, but can be
	peopleNames VARCHAR(100) NOT NULL,
	InsertedDate DATETIME NOT NULL DEFAULT CAST(GETDATE() AS DATETIME),
	);
	INSERT INTO #someTable (peopleNames) VALUES
	('Quill')
	,('Jill')
	,('Bill');
	
	DECLARE @compilation VARCHAR(MAX);
	DECLARE @step INT = 1;
	DECLARE @stepMax INT = (SELECT MAX(rNumber) FROM #someTable);
	WHILE (@step <= @stepMax)
	BEGIN
		SELECT
			@compilation = CONCAT(@compilation, ',', peopleNames)
		FROM #someTable WHERE rNumber = @step;
		-- Increment for next row
		SET @step = @step + 1;
	END

	-- While newer versions of SQL support DROP IF EXISTS syntax, this older method will work as well if backwards-compatibility is a concern
	IF OBJECT_ID('tempdb..#someTable') IS NOT NULL
	BEGIN
		DROP TABLE #someTable;
	END

	SELECT @compilation; -- Return the results

The STORED PROCEDURE:
Whether a CURSOR or a WHILE (as shown above) is used is up to you. For this tutorial I'll simply use the WHILE in a stored procedure so you can see how the two can be used together.

	CREATE PROCEDURE [dbo].[myProc]
	(
	@result VARCHAR(MAX) OUTPUT
	)
	AS
	BEGIN
		SET NOCOUNT ON;
		CREATE TABLE #someTable
		(
		rNumber INT NOT NULL IDENTITY(1, 1), -- Does not need to be a primary key, but can be
		peopleNames VARCHAR(100) NOT NULL,
		InsertedDate DATETIME NOT NULL DEFAULT CAST(GETDATE() AS DATETIME),
		);
		INSERT INTO #someTable (peopleNames) VALUES
		('Quill')
		,('Jill')
		,('Bill');

		DECLARE @compilation VARCHAR(MAX);
		DECLARE @step INT = 1;
		DECLARE @stepMax INT = (SELECT MAX(rNumber) FROM #someTable);
		WHILE (@step <= @stepMax)
		BEGIN
			SELECT
				@compilation = CONCAT(@compilation, ',', peopleNames)
			FROM #someTable WHERE rNumber = @step;
			-- Increment for next row
			SET @step = @step + 1;
		END

		-- While newer versions of SQL support DROP IF EXISTS syntax, this older method will work as well if backwards-compatibility is a concern
		IF OBJECT_ID('tempdb..#someTable') IS NOT NULL
		BEGIN
			DROP TABLE #someTable;
		END

		SET @result = @compilation; -- Return the results
	END

Once the stored procedure is created, you can call it and grab the @result like this:

	DECLARE @spResults VARCHAR(MAX) = NULL;
	EXECUTE [dbo].[myProc] @spResults OUTPUT;
	SELECT @spResults;