Understanding “Set based” and “Procedural” approaches in SQL

Nice article by M.M.Al-Farooque Shubho

use Northwind


go

--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Products'
--Declare the Table variable 
DECLARE @Elements TABLE
(
        Number INT IDENTITY(1,1), --Auto incrementing Identity column
        ProductName VARCHAR(300) --The string value
)

--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT 

--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT

--Populate the TABLE variable using some logic
INSERT INTO @Elements SELECT ProductName FROM dbo.Products

--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1

--Determine the number of rows in the Table
SELECT @Count=max(Number) from @Elements

--A variable to hold the currently selected value from the table
DECLARE @CurrentValue varchar(300);

--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count

BEGIN
    --Load current value from the Table
    SELECT @CurrentValue = ProductName FROM @Elements WHERE Number = @CurrentDelimiterPositionVar
    --Process the current value
    print @CurrentValue
    --Increment loop counter
    SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END