How to implement LOOPS in SQL Server using CURSOR

Guys,

Sometimes you all must be required to have loops in your SQL Statements or Stored Procedures.

Getting tensed?

No need to get tensed. Here is the solution.

We would use Cursors to solve this issue.

Cursor gives us facility to iterate each row in a table and we could do operations on it.

Here is the T-SQL  query.

--Check if temp Table already exists
IF OBJECT_ID('tempdb..#FinalTable') IS NOT NULL
BEGIN
DROP TABLE #FinalTable;
END
 
CREATE TABLE #FinalTable (
SubjectName NVARCHAR(100),
NoOfStudents INT, AverageMarks DECIMAL(18,2) NULL );

--Declare the cursor
DECLARE Student_Cursor CURSOR FOR 
SELECT DISTINCT E.SubjectName
FROM dbo.Engagement AS E
INNER JOIN dbo.Subjects AS Sub ON E.SubjectID = Sub.SubjectID
INNER JOIN dbo.Marks AS Mark ON Mark.SubjectId = Sub.SubjectId 
WHERE SubjectName LIKE 'ABC_%' OR SubjectName LIKE 'XYZ_%' OR SubjectName LIKE '123_%';

--Cursor Opens
OPEN Student_Cursor;
--Gets first Value
FETCH NEXT FROM Student_Cursor
INTO @SubjectName
--Starts loop
WHILE @@FETCH_STATUS = 0
BEGIN     
IF(@SubjectName NOT LIKE '')
BEGIN
      SELECT @NoOfStudents = COUNT(Tbl.Marks), @AverageMarks = (SUM(Tbl.Marks)/@NoOfStudents)
      FROM SubjectMarks AS Tbl
      WHERE Tbl.Marks >= 0
                     AND Tbl.SubjectName LIKE @SubjectName

     -- Insert values into Temp table.
     INSERT INTO #FinalTable VALUES (@SubjectName, @NoOfStudents, @AverageMarks)

--Reset values (Not Necessary)
SET @SubjectName = '';
SET @NoOfStudents = 0;
SET @AverageMarks = 0;
FETCH NEXT FROM Student_Cursor
INTO @SubjectName

END
END;

--Close and deallocate the cursorCLOSE Student_Cursor;
DEALLOCATE Student_Cursor;

Doing this you could achieve the iterating any table and doing operation on it. Also you can save these values into new tables.