Iteration MS Sql | Cursor | While loop

 CURSOR

In SQL Server stored procedures, you can use a `CURSOR` to iterate over the result set row by row. Here's an example of how to use a `CURSOR` in a SQL Server stored procedure to iterate through the result set:

```sql

-- Create a temporary table to hold the result set

CREATE TABLE #TempTable

(

    ID INT,

    Name NVARCHAR(255)

);

-- Insert sample data into the temporary table

INSERT INTO #TempTable (ID, Name)

VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');

-- Declare variables to hold values from the result set

DECLARE @ID INT;

DECLARE @Name NVARCHAR(255);

-- Declare a CURSOR for the result set

DECLARE myCursor CURSOR FOR

SELECT ID, Name FROM #TempTable;

-- Open the CURSOR

OPEN myCursor;

-- Fetch the first row into variables

FETCH NEXT FROM myCursor INTO @ID, @Name;

-- Loop through the result set

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Do something with @ID and @Name (process each row)

    PRINT 'ID: ' + CAST(@ID AS NVARCHAR(10)) + ', Name: ' + @Name;

    -- Fetch the next row

    FETCH NEXT FROM myCursor INTO @ID, @Name;

END;

-- Close and deallocate the CURSOR

CLOSE myCursor;

DEALLOCATE myCursor;

-- Drop the temporary table

DROP TABLE #TempTable;

```

In this example:

1. We create a temporary table `#TempTable` and insert sample data into it. This table will serve as our result set.

2. We declare variables `@ID` and `@Name` to hold values from the result set.

3. We declare a `CURSOR` named `myCursor` for the `SELECT` query on `#TempTable`.

4. We open the `CURSOR`, fetch the first row into variables, and then enter a loop.

5. Inside the loop, we process each row (you can replace the `PRINT` statement with your logic).

6. We fetch the next row until there are no more rows to fetch.

7. Finally, we close and deallocate the `CURSOR` and drop the temporary table.

This code demonstrates how to iterate through a result set using a `CURSOR` in a SQL Server stored procedure. Please adapt it to your specific requirements and result set. Cursors should be used judiciously, as they can impact performance for large result sets.

`WHILE` loop

Iterating through a result set in a SQL Server stored procedure using a `CURSOR` is a common method, but if you're looking for an alternative approach, you can use a `WHILE` loop combined with the `ROWCOUNT` function to achieve a similar result. Here's an example:

```sql

-- Create a temporary table to hold the result set

CREATE TABLE #TempTable

(

    ID INT,

    Name NVARCHAR(255)

);

-- Insert sample data into the temporary table

INSERT INTO #TempTable (ID, Name)

VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');

-- Declare variables to hold values from the result set

DECLARE @ID INT;

DECLARE @Name NVARCHAR(255);

-- Initialize a variable to track the row number

DECLARE @RowCount INT = 1;

-- Get the total number of rows in the result set

DECLARE @TotalRows INT = (SELECT COUNT(*) FROM #TempTable);

-- Loop through the result set using a WHILE loop

WHILE @RowCount <= @TotalRows

BEGIN

    -- Retrieve values for the current row

    SELECT @ID = ID, @Name = Name

    FROM (

        SELECT ID, Name, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum

        FROM #TempTable

    ) AS SubQuery

    WHERE RowNum = @RowCount;

    -- Do something with @ID and @Name (process each row)

    PRINT 'ID: ' + CAST(@ID AS NVARCHAR(10)) + ', Name: ' + @Name;

    -- Increment the row count

    SET @RowCount = @RowCount + 1;

END;

-- Drop the temporary table

DROP TABLE #TempTable;

```

In this alternative approach:

1. We create a temporary table `#TempTable` and insert sample data into it.

2. We declare variables `@ID` and `@Name` to hold values from the result set.

3. We initialize `@RowCount` to 1 to keep track of the current row number.

4. We use the `ROW_NUMBER()` function to assign a row number to each row in the result set.

5. We get the total number of rows in the result set using `@TotalRows`.

6. We enter a `WHILE` loop that iterates as long as `@RowCount` is less than or equal to `@TotalRows`.

7. Inside the loop, we retrieve values for the current row based on the row number using a subquery.

8. We process each row (you can replace the `PRINT` statement with your logic).

9. We increment `@RowCount` to move to the next row.

10. Finally, we drop the temporary table.

This approach avoids using a `CURSOR` but still allows you to iterate through a result set row by row. It's generally suitable for smaller result sets, and you should consider performance implications for larger result sets.