Wednesday, January 1, 2014

While loop in SQL server


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'

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, 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 :-). 

DMCA.com

3 comments:

  1. 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.

    ReplyDelete
  2. 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.

    ReplyDelete
    Replies
    1. Celko,
      If you don't have ID you can do row_number over partition
      based on FirstName LastName
      Thanks,
      Ed

      Delete