Use a While Loop Instead of Cursors in SQL | Part 22

Use a While Loop Instead of Cursors in SQL Server:
Cursor is a Temporary Memory or Temporary Work Station. It is Allocated by Database Server at the Time of Performing DML(Data Manipulation Language) operations on Table by User. Cursors are used to store Database Tables

A cursor is a database object which can hold more than one row but it can process only one row at a point of time. The set of rows held by a cursor holds is known as an active set. So you can control the record of a table in a singleton technique i.e one row at any point in time.

Now let us how implement the while loop in place of cursor, let us see:

CREATE TABLE [Employee](
[empid] int ,
[emp_name] [nvarchar](100) NULL,
[salary] [float] NULL,
emp_city nvarchar(100),
constraint pk_employee primary key clustered
(empid asc)
) ON [PRIMARY]

Use of while loop:
Syntax of while LOOP
DECLARE @count INT;
SET @count = 1;

WHILE @count less sign put here= 5
BEGIN
PRINT @count
SET @count = @count + 1;
END;

Example 1
Declare @MinRowsCount int
Declare @MaxRowsCount int
Declare @emp_name nvarchar(100)

-- get a minimum and maximum value in the table Empoyee
SELECT @MinRowsCount = min(empid), @MaxRowsCount=max(empid) FROM [Employee];
print @MinRowsCount
print @MaxRowsCount

begin

-- print the emp name based upon the empid through the while loop
print @emp_name

end

Example 2:

Declare @MinRowsCount int
Declare @MaxRowsCount int
Declare @emp_name nvarchar(100)

--drop table #temptable
create table #temptable (empid int identity(1,1),emp_name nvarchar(100),salary float,emp_city nvarchar(100))

insert into #temptable select emp_name, salary, emp_city from [Employee]

-- get a minimum and maximum value in the table # temptable
SELECT @MinRowsCount = min(empid), @MaxRowsCount=max(empid) FROM #temptable;
print @MinRowsCount
print @MaxRowsCount

begin

-- print the emp name based upon the empid through the while loop
print @emp_name

end