While loop in SQL server
Introduction
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.
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' The data contains by the table tbl_Empdata is given below:-
select * from tbl_Empdata
Business logic to be implemented in the Stored Procedure
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, you can read the article Exceptional Handling in SQL Server better understanding.
Summary
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 :-).
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
It's articles/blog entire like these that show how some people should not be writing when they cannot clearly explain or understand when and why you should or should not use a technique.
Here's a perfect example where your while loop will suffer. I have a table of 5M records out of those, I need to update only 5,000. In your while loop you will hit the table(s) 5,000 times instead of once. That just doesn't make any sense.
When you create a loop/cursor, you are typically working with a tight subset of data. There should never be an issue with too much RAM being consumed.
Also, if you are constantly executing the same query, the tables are automatically loaded into RAM to expedite execution. That means there will be significantly more RAM used vs a cursor.
Please learn ISO-11179 Standards for data element names. Nothing you posted was right or made sense. You used IDENTITY, have no keys or constraints, think that people have hundred characters for everything. No idea what the ISO Standard codes for sex are, ISO-8601 for dates (I had to correct them by hand because you have no idea what ANSI/ISO Standard SQL is and that we do not use CURRENT_TIMESTAMP), etc. You even tibbled!
Tables are sets, so their names are collective or plural nouns. This non-table is not even normalized. You mixed Personnel with jobs; they are different entities with a relationship (job assignments)
Why do you think that “Designations” are a clearly defined SET of entities? It looks like a job title to me; and we use the DOT codes for them, not that IDENTITY you used.
This is how an SQL programmer would write the table:
CREATE TABLE Job_Titles
(dot CHAR(6) NOT NULL PRIMARY KEY
CHECK (dot) LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]-[0-9][0-9][0-9]'),
job_title VARCHAR(100) NOT NULL);
Why do you have a national id, a non-relational count of the physical insertion attempts and a local emp_id that is the wrong data type for an identifier? And none of them are keys!! Try this:
CREATE TABLE Personnel
(national_id_nbr CHAR(10) NOT NULL PRIMARY KEY,
first_name NVARCHAR(20),
last_name NVARCHAR(20),
birth_date DATE NOT NULL,
CHECK (birth_date < hire_date),
marital_status CHAR(1) NOT NULL
CHECK (marital_status IN ('A', 'D', 'I', 'L', 'M', 'P', 'S', 'T', 'W')),
sex_code CHAR(1) NOT NULL
CHECK (sex_code IN ('0', '1', '2', '9'))
);
This is a relationship table:
CREATE TABLE Job_assignments
(national_id_nbr CHAR(10) NOT NULL
REFERENCES Personnel
ON UPDATE CASCADE
ON DELETE CASCADE,
login_id VARCHAR(100) NOT NULL,
pass_word VARCHAR(20) NOT NULL,
hire_date DATE NOT NULL,
termination_date DATE — null means current job
job_title NVARCHAR(20) NOT NULL);
>> Business logic to be implemented in the Stored Procedure: <<
NO! We want to use Declarative Referential Integrity to enforce business rules, not procedural code. We think in sets and not in row-by row coding. That was COBOL in the 1960's, not SQL.
For every row
1) Check if the job title is existed in the system with the job title_name given in the row or not. IF not then create it.
NO! We declare up a REFERENCES constraint to assure that a job title is valid. If it does not exist, reject it; NEVER create it automatically. Your database will fill with typos and garbage data! That needs to be a separate procedure. This is not SQL; it is basic Software Engineering.
Check if the employee is new then create the new employee and insert the employee entry into corresponding tables.
Check if the employee is already existed in the system, then update the employee information in the tables.
These rules are a MERGE statement. Your procedure name is wrong (more tibling!) and the use of local variables and control flow in a declarative language is totally wrong. And the use of bit flags is assembly language programming and has no place in RDBMS.
You would get a table with the new Human Resources data do all of this in one declarative statement. Here is the skeleton:
CREATE PROCEDURE Update_Personnel
AS
BEGIN
MERGE INTO Personnel AS P
USING HR_Data AS N — need a table
ON P.national_id_nbr = N.national_id_nbr
WHEN MATCHED
THEN UPDATE …
WHEN NOT MATCHED
THEN INSERT …
END;
You even put commas a the start of line of code. That is how we wrote COBOL and FORTRAN on punch cards. Why do you think that comments like “/*END OF THE TRY CODE BLOCK*/” gives useful information to the person maintaining the code?
You did the wrong things with the most awful code I have seen in months.
Celko,
If you don't have ID you can do row_number over partition
based on FirstName LastName
Thanks,
Ed