Using WHILE loop to loop through the records in a table.

CREATE PROC spLoop
AS
BEGIN
DECLARE @Counter INT = 1
DECLARE @TableCount INT
DECLARE @SQLStmt NVARCHAR(MAX)= ''
DECLARE @TEST TABLE
(
NAME NVARCHAR(MAX))
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#NEWTEMP]') AND type in (N'U'))
DROP TABLE [dbo].#NEWTEMP
INSERT INTO @TEST(NAME) VALUES('TEST-A')
INSERT INTO @TEST(NAME) VALUES('TEST-B')
CREATE TABLE #TEMP(ID INT IDENTITY(1,1),NAME NVARCHAR(MAX))
INSERT INTO #TEMP(NAME) SELECT NAME FROM @TEST
SELECT @TableCount = COUNT(1) FROM @TEST
WHILE(@Counter <= @TableCount)
BEGIN
SET @SQLStmt = @SQLStmt + 'SELECT NAME FROM #TEMP WHERE ID =' + CONVERT(NVARCHAR,@Counter) + ';'
SET @Counter = @Counter + 1
END
SELECT LEFT(@SQLStmt, LEN(@SQLStmt) - 1)
END
----------------------------------------------------------------------
EXEC spLoop