Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

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

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...