In While loop, we set a condition for the repeated execution of a SQL Code block or a SQL Statement. The SQL Statement or SQL Block keep on repeatedly executing till the condition in the while loop is satisfied. Once the condition is failed, the repeated execution of the block stop immediately. We can also used Break and Continue command within the while loop.
The Syntax of the While loop is given below:-
While(condition)
Begin
// SQL Statement / SQL code block
End
We can also use the while loop to avoid cursor in store procedure to increase the performance. In case of cursor, it stored all the data into the memory which is return by the query used within cursor and then fetch the rows from the memory one by one. But if the data return by the cursor is large , it effect the performance adversely since the major portion of the RAM is occupied to store that data and memory is not free till the cursor is deallocated . But in case of while loop, it put only one row at a time into the memory and thus increase the performance a lot.
Now with the help of the example, I am trying to show you how we can use the while loop in the real scenario.
For Example:- Suppose we needs to import data from a text file and then according to our predefined business logic, insert that data into our database tables. Suppose in first step we read the data from the text file and insert the data into the table tbl_Empdata. In 2nd step, we need to write a store procedure which read the records from this table one by one, implement the predefined business logic and accordingly insert the data into the database tables.
Structure of the table tbl_Empdata is given below:-
Create table tbl_Empdata (id int identity(1,1) , Empid int, NationalIdNumber varchar(100),EmpFirstname nvarchar(100),Emplastname nvarchar(100),Birthdate datetime,Hiredate datetime,MaritalStatus nvarchar(10), Loginid nvarchar(100), emppassword nvarchar(20),Gender nvarchar(10),Enddate datetime,designationname nvarchar(200))
Database tables in which data is going to be inserted is given below
Create table tbl_Designation (designationid int identity(1,1), Designationname nvarchar(150))
Create table tbl_Employee(Empid int identity(1,1), NationalIdNumber varchar(100) ,EmpFirstname nvarchar(100),Emplastname nvarchar(100),Birthdate datetime,Hiredate datetime,MaritalStatus nvarchar(10), Loginid nvarchar(100), emppassword nvarchar(20),Gender nvarchar(10),Enddate datetime)
Create table tbl_emp_designation (empdesignationid int identity(1,1), empid int,designationid int, startdate datetime)
Insert Query for insert the data into the table tbl_Empdata
insert into tbl_Empdata( NationalIdNumber ,EmpFirstname ,Emplastname ,Birthdate ,Hiredate ,MaritalStatus , Loginid , emppassword ,Gender ,Enddate,designationname )
Select '11121', 'Neeraj','Kumar','01-01-1980','01-02-2006','M','11121','nee121','M',null,'Project Manager'
union all
Select '11828', 'Vivek','Johari','01-01-1982','01-02-2006','S','11828','viv828','M',null,'Tech lead'
union all
Select '11000', 'Avinash','Dubey','03-01-1986','01-02-2006','S','11000','avi000','M',null,'Senior Software Engineer'
union all
Select '120087','Chandra','Singh','01-11-1984','01-02-2007','S','120087','cha087','M',null,'Senior Software Engineer'
union all
Select '120001','Gaurav', 'Negi','01-01-1990','01-02-2012','S','120001','gau001','M',null,'Software Engineer'
union all
Select '11220', 'Uma','Sharma','01-01-1982','01-02-2006','M','11220','uma020','M',null,'Senior QA Engineer'
union all
Select '120012','Kapil','Kumar','01-01-1982','01-02-2006','S','120012','kap012','M',null,'Software Engineer'
union all
Select '232212','Puneet','Kumar','01-01-1992','01-02-2009','S','232212','pun212','M',null,'QA Engineer'
union all
Select '111221','Reema','Kaur','01-01-1984','01-02-2012','M','111221','rem221','F',null,'Software Engineer'
union all
Select '112231','Neha','Gupta','01-01-1983','01-02-2007','S','112231','neh231','F',null,'Software Engineer'
union all
Select '220012','Anil','Kumar','01-06-1982','01-02-2006','M','220012','ani012','M',null,'Software Engineer'
union all
Select '220092','Ajay','Thakur','01-05-1982','01-02-2006','S','220092','aja092','M',null,'Software Engineer'
union all
Select '224001','Jitendra','Kumar','01-01-1982','01-06-2006','M','224001','jit001','M',null,'Software Engineer'
union all
Select '225001','Amit','Singh','01-01-1989','01-02-2011','S','225001','ami001','M',null,'Manager Admin'
union all
Select '225002','Anju','Sharma','01-01-1982','01-02-2006','M','225002','anj002','F',null,'HR Manager'
union all
Select '224993','Reeta','Gupta','01-01-1990','01-02-2010','S','224993','ree993','F',null,'Marketing Head'
union all
Select '223301','Manisha','Sharma','01-01-1996','01-02-2012','S','223301','man301','F',null,'Director-HR'
select * from tbl_Empdata
For every row
1) Check if the designation is existed in the system with the designationname given in the row or not. If not then create it.
2) Check if the employee is new then create the new employee and insert the employee entry into corresponding tables.
3) Check if the employee is already existed in the system, then update the employee information in the tables.
Script for the Stored procedure
Create procedure usp_Empimport
as
begin
/* Declaration of the variables used in Stored Procedure */
Declare @min_id as int
Declare @isdesignationexists as int
Declare @NationalIdNumber as varchar(100)
Declare @designationname as varchar(100)
Declare @designationid as int
Declare @isempexists as int
Declare @EmpFirstname as nvarchar(200)
Declare @empid as int
Declare @iseemp_designation_assign as int
Declare @Emplastname as nvarchar(200)
Declare @Birthdate as datetime
Declare @Hiredate as datetime
Declare @MaritalStatus as nvarchar(20)
Declare @Loginid as nvarchar(50)
Declare @emppassword as nvarchar(50)
Declare @Gender as nvarchar(10)
Declare @Enddate as datetime
/* Start of the transaction*/
Begin TRANSACTION
/* Start of the try block*/
Begin Try
select @min_id=min(id) from tbl_Empdata
/* start of the while loop section*/
While (@min_id>0)-- Condition for repeatedly execution of the SQL Block of code within While loop
Begin
select @NationalIdNumber=NationalIdNumber,@EmpFirstname=EmpFirstname ,@Emplastname=Emplastname ,@Birthdate=Birthdate ,@Hiredate=Hiredate,
@MaritalStatus=MaritalStatus , @Loginid=Loginid, @emppassword=emppassword,@Gender=Gender,@Enddate=Enddate,@designationname=designationname from
tbl_Empdata where id=@min_id
/* Code to stop the import of data from the rows where nationalIDNumber and designationname is null or emply*/
If (@NationalIdNumber is Not null and @NationalIdNumber<>'') and (@designationname is not null and @designationname<>'')
begin
/*SQL code block to check whether the designation exists in the system*/
select @isdesignationexists=count(designationid)from tbl_Designation where Designationname=@designationname
if @isdesignationexists=0
begin
insert into tbl_Designation(Designationname)
values(@designationname)
end
select @designationid=designationid from tbl_Designation where Designationname=@designationname
/*SQL code block to check whether the employee exists in the system*/
select @isempexists=count(empid) from tbl_Employee where NationalIdNumber=@NationalIdNumber
if @isempexists=0
begin
/* If employee not exists then create the new entry for it*/
insert into tbl_Employee(NationalIdNumber ,EmpFirstname ,Emplastname ,Birthdate ,Hiredate,MaritalStatus , Loginid, emppassword,Gender,Enddate )
select NationalIdNumber ,EmpFirstname ,Emplastname ,Birthdate ,Hiredate, MaritalStatus ,Loginid, emppassword,Gender,Enddate from tbl_Empdata
where id=@min_id
end
else if @isempexists>0
begin
/* If employee exists then update his/her information*/
update tbl_Employee set EmpFirstname=@EmpFirstname ,Emplastname=@Emplastname ,Birthdate=@Birthdate ,Hiredate=@Hiredate,
MaritalStatus=@MaritalStatus , Loginid=@Loginid, emppassword=@emppassword,Gender=@Gender,Enddate=@Enddate where NationalIdNumber=@NationalIdNumber
end
select @empid=empid from tbl_Employee where NationalIdNumber=@NationalIdNumber
select @iseemp_designation_assign=count(empdesignationid) from tbl_emp_designation where empid=@empid and designationid = @designationid
if @iseemp_designation_assign=0
begin
/* insert the entry into the table tbl_emp_designation to assign current designation to the employee*/
insert into tbl_emp_designation(empid,designationid,startdate)
values(@empid,@designationid,GETDATE()) -- Getdate() function is used to get the current database serverdate
end
end
select @min_id=isnull(min(id),0) from tbl_Empdata where id>@min_id
End
/*End of the Try code block*/
End Try
/*Start of the Catch code block*/
Begin Catch
/* In case of any error, check if any transaction occur and if any transaction takes place the rollback it*/
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
,GETDATE()
End Catch
/* In case of no error, commit the transaction*/
IF @@TRANCOUNT > 0
Begin
Commit Transaction
End
End
Now we execute the above stored procedure with the help of the following command
Exec usp_Empimport
After the execution of the procedure we get the following data in the database tables
select * from tbl_Employee
select * from tbl_Designation
select * from tbl_emp_designation
The above stored procedure is easy to understand due to comments write in it. Also from this stored procedure we learn other important things also like
1) Error handling in the store procedure using Try... Catch
2) How to use Transaction...Rollback inside the stored procedure
3) Use of If ...Else
Although I have try to give introduction of error handling, transaction..Rollback and if.. else through the use of proper comments, I will going to write separate articles on each one of this topic soon to give better understanding.
In this article, I am tried to explain the while loop feature of the SQL Server with the help of the example. If anyone has any doubt about it, he/she can send me a mail on my mail id askvivekjohari@gmail.com. I will reply as soon as possible.
Keep Reading Keep Learning :-).
