Saturday, January 11, 2014

Question of the Week - 11/1/2014

Question:- Suppose there are two tables A and B and we need to write 3 SQL queries which returns the record set as shown in the below figure.


First Query:-

In this case, you have to write a SQL Query using SQL JOIN which return the data which is not common to these two tables A& B as shown in the shaded part in 1st figure.


Second Query:-

 In this case, you have to write a SQL Query using SQL JOIN which will return the data from table A which is not in the table B as shown as shaded part in 2nd figure.



Third Query:-

 In this case, you have to write a SQL Query using SQL JOIN which will return the data from table B which is not in the table A as shown as shaded part in 3rd figure.








Previous Questions and their Answers


 Question of the Week - 2/1/2014 - Question 1
  
 Answer

 Question of the Week - 28/12/2013 - Question 2

 Answer

 Question of the Week -25/12/2013- Question1


 Answer

DMCA.com

Answer - Objective Question (In which database temporary tables are created in SQL Server)

Question :- In which database temporary tables are created in SQL Server?

1) User database (where temporary table are defined)
   
2) Master database
   
3) TempDB (Correct Answer)

4) Model Database

5) None of the above

Answer
 Thanks to all who attempted this question. 84% people gives the correct answer:-)

Correct answer is option 3, TempDB. All the temporary tables and table variables are created in the TempDB.

Related Articles

SQL Server - Local temporary table

SQL Server - Global temporary tables




DMCA.com

Friday, January 10, 2014

Answer -Question of the Week - 2/1/2014 - Question 1

Question :-

Suppose we have to design a database in which employees can work on multiple projects.There are many designation defined in the organization to which each employee can belongs.
 

For example, Software engineer, Team lead, Project Manager, Project lead, QC engineer, QC Lead, QC manager etc. An employee can belong to one designation
at a time.  It means at a time an employee can either be Software engineer or team lead or project manager but not software engineer as well team lead at a same time.

Also the projects can work on different technologies like .Net, SQL Server, Oracle, HTML5 etc. Every project has a start data and end date. A project can work can include many technologies like .net, SQL Server , Java script, HTML5 etc. Most of the time every project work include a front end server technology like JAVA. Asp.net, uses Java script, HTML, CSS as client side technologies and SQL Server, oracle as back end database server. So In this ways a project work can include many technologies as in above example,  suppose a project can use Asp.net as server end front end technology, java script, HTML, CSS , AJAX as client side technology and SQL server as back end database server.

What should be the design the database schema (tables, their foreign keys, primary keys etc.

Answer:-

In this Question, main challenge is to how we can identified the main objects whose information is stored in master tables and how we can identified the transaction (child) tables.

In the above scenario, we can identified 4 different main objects whose information should be contained in the master tables. They are given below:-



1) Employee

In this master table, we store the information about the employee, like name, date of joining etc.

CREATE TABLE [dbo].[tbl_employees](
    [employeeid] [int] IDENTITY(1,1) NOT NULL,
    [firstname] [nvarchar](100) NULL,
    [lastname] [nvarchar](100) NULL,
    [regdatum] [datetime] NULL,
    [email] [nvarchar](100) NULL,
 CONSTRAINT [PK_tbl_employees] PRIMARY KEY CLUSTERED
(
    [employeeid] ASC
) )ON [PRIMARY]




Suppose we have to store the employee data as shown in the above picture then the insert command for the table tbl_employee will be as follows:

Insert into tbl_employees(firstname,lastname,regdatum,email)
select 'Vivek','Johari','02/01/2007','vivek@xyz.com'
union all
select 'Mohit','Rathi','03/01/2013','mohit@xyz.com'
union all
select 'Aviral','Lamba','05/01/2007','aviral@xyz.com'
union all
select 'Chandra','Singh','10/01/2010','chandra@xyz.com'
union all
select 'Abhinav','Golwalkar','01/01/2007','abhinav@xyz.com'
union all
select 'Gaurav','Negi','05/01/2013','gaurav@xyz.com'




2) Projects

In this master table,we store the information about the projects like their name, start date, end date

CREATE TABLE [dbo].[tbl_project](
    [projectid] [int] IDENTITY(1,1) NOT NULL,
    [projectname] [nvarchar](200) NULL,
    [startdate] [datetime] NULL,
    [enddate] [datetime] NULL,
 CONSTRAINT [PK_tbl_project] PRIMARY KEY CLUSTERED
(
    [projectid] ASC
)
) ON [PRIMARY]
 





 Similarly, suppose we have to store the above projects information as shown in the above picture then the insert command for the table tbl_project will be given below:


 Insert into tbl_project(projectname,startdate, enddate)
select 'Learninglms','02/01/2007','10/01/2010'
union all
select 'Bankpds','05/01/2013','05/01/2014'
union all
select 'ERPsystem','05/01/2012','10/01/2014'



3) Designation

In this master table, we store information about the various designations of the company which they have in their hierarchy.

CREATE TABLE [dbo].[tbl_designation](
    [designationid] [int] IDENTITY(1,1) NOT NULL,
    [designationname] [nvarchar](150) NULL,
 CONSTRAINT [PK_tbl_designation] PRIMARY KEY CLUSTERED
(
    [designationid] ASC
)
) ON [PRIMARY]




Similarly, to store all the designation of the company as shown in above figure, the insert command for the table tbl_designation is given below:

Insert into tbl_designation(designationname)
select 'Trainee'
union all
select 'Software Engineer'
union all
Select 'Tech Laed'
union all
select 'Senior Tech lead'
union all
select 'Project manager'



4) Technology

In this master table, we store the information about various technologies like name of the technology

CREATE TABLE [dbo].[tbl_technology](
    [technologyid] [int] IDENTITY(1,1) NOT NULL,
    [technologyname] [nvarchar](150) NULL,
 CONSTRAINT [PK_tbl_technology] PRIMARY KEY CLUSTERED
(
    [technologyid] ASC
)
) ON [PRIMARY]






Similarly, to store all the various technologies used in the company (shown in the above figure), the insert command for the table tbl_technology is given below:


Insert into tbl_technology(technologyname)
select 'JAVA'
union all
select 'C#'
union all
select 'CSS'
union all
select 'JQUERY'
union all
select 'SQL Server'
union all
select 'ORACLE'
union all
select 'PHP'
union all
select 'HTML5'



Employee-Project relationship

In any company, an employee can work in multiple project and in one project multiple employees can work. So there is many to many relationship between employees and projects. In these case we need to create a transaction(child or bridge) table to store these relationship whose structure is given below:-

CREATE TABLE [dbo].[tbl_employee_project](
    [empprojectid] [int] IDENTITY(1,1) NOT NULL,
    [employeeid] [int] NULL,
    [projectid] [int] NULL,
    [allocationstartdate] [datetime] NULL,
    [allocationenddate] [datetime] NULL
) ON [PRIMARY]

GO


Foreign Key relationship with their master table is given below:-


Here, employeeid of the table tbl_employee_project refers(foreign key) to employeeid of the master table tbl_employees.

ALTER TABLE [dbo].[tbl_employee_project]  WITH CHECK ADD  CONSTRAINT [FK_tbl_employee_project_tbl_employees] FOREIGN KEY([employeeid])
REFERENCES [dbo].[tbl_employees] ([employeeid])
GO

ALTER TABLE [dbo].[tbl_employee_project] CHECK CONSTRAINT [FK_tbl_employee_project_tbl_employees]
GO


Here, projectid of the table tbl_employee_project refers(foreign key) to projectid of the master table tbl_project.

ALTER TABLE [dbo].[tbl_employee_project]  WITH CHECK ADD  CONSTRAINT [FK_tbl_employee_project_tbl_project] FOREIGN KEY([projectid])
REFERENCES [dbo].[tbl_project] ([projectid])
GO

ALTER TABLE [dbo].[tbl_employee_project] CHECK CONSTRAINT [FK_tbl_employee_project_tbl_project]
GO




Now suppose we want to allocate the employees to the various projects as shown in the above figure then the insert command for the table  tbl_employee_project is given below:

Insert into tbl_employee_project (employeeid,projectid, allocationstartdate, allocationenddate)
select 1,2,'05/01/2013',null
union all
select 1,3,'05/01/2012','05/20/2013'
union all
select 2,1,'02/01/2007','10/01/2010'
union all
select 3,1,'02/01/2007','10/01/2010'
union all
select 4,3,'05/01/2012',null
union all
select 5,2,'05/01/2013',null
union all
select 5,3,'05/01/2012',null
union all
select 6,3,'05/01/2012',null


Here in these insert command, we use the employeeid of the employees taken from table tbl_employees and projectid of the projects taken from the table tbl_project.


Employee designation relationship

In a company, an employee can have one designation at a time . For example, an employee can't be software engineer or tech lead at a same time in the company. He is either software engineer or tech lead. But we also need to store the history of the  employee previous designation so that we can maintain the record that 6 months back, at which designation, a particular employee is work in project. For example , suppose 6 month back employee, "Vivek" work as software engineer to an project "ABC LMS" and now he become tech lead and working in another project "XYZ LMS" as tech lead. So to maintain that history, we have to store the employee designation relationship in a separate transaction table. Now a employee designation within a longer duration of time can change from one position to another position and on same designation, multiple employee can work, there is many to many relationship.

The structure to store this relationship is given below:-

CREATE TABLE [dbo].[tbl_emp_designation](
    [empdesignationid] [int] IDENTITY(1,1) NOT NULL,
    [employeeid] [int] NULL,
    [designationid] [int] NULL,
    [allocationdate] [datetime] NULL
) ON [PRIMARY]

GO


Foreign Key relationship with their master table is given below:-

Here, designationid of the table tbl_emp_designation refers(foreign key) to designationid of the master table tbl_designation.

ALTER TABLE [dbo].[tbl_emp_designation]  WITH CHECK ADD  CONSTRAINT [FK_tbl_emp_designation_tbl_designation] FOREIGN KEY([designationid])
REFERENCES [dbo].[tbl_designation] ([designationid])
GO

ALTER TABLE [dbo].[tbl_emp_designation] CHECK CONSTRAINT [FK_tbl_emp_designation_tbl_designation]
GO


Here, employeeid of the table tbl_emp_designation refers(foreign key) to employeeid of the master table tbl_employees.


ALTER TABLE [dbo].[tbl_emp_designation]  WITH CHECK ADD  CONSTRAINT [FK_tbl_emp_designation_tbl_employees] FOREIGN KEY([employeeid])
REFERENCES [dbo].[tbl_employees] ([employeeid])
GO

ALTER TABLE [dbo].[tbl_emp_designation] CHECK CONSTRAINT [FK_tbl_emp_designation_tbl_employees]




Now suppose we want to allocate designation to all the employees as shown in the above figure then the insert command for the table  tbl_emp_designationis given below:

Insert into tbl_emp_designation(employeeid,designationid,allocationdate)
select 1,3,'01/06/2012'
union all
select 1,4,getdate()
union all
select 2, 2, '01/06/2012'
union all
select 3,1,'01/06/2013'
union all
select 4,2,'10/01/2010'
union all
select 4,3,getdate()
union all
select 5,3 ,'01/01/2007'
union all
select 5,4,'01/06/2012'
union all
select 5,5,getdate()
union all
select 6,2,'05/01/2013'


Here also we use the employeeid of each employee from the table tbl_employees and designationid from the table tbl_designation.


Employee technology relationship

An employee can have the knowledge of more than one technology. For example , an employee can know, SQL server, Asp.Net, CSS, JQuery, JAVA Script, C# and on a particular technology can be known by multiple employees, so there is again many to many relationship exists between employees and technologies. Therefore we need a transaction table to store the relationship between the employee and technologies.


CREATE TABLE [dbo].[tbl_employee_technology](
    [emptechnologyid] [int] IDENTITY(1,1) NOT NULL,
    [employeeid] [int] NULL,
    [technologyid] [int] NULL
) ON [PRIMARY]

GO


Foreign Key relationship with their master table is given below:-


Here, employeeid of the table tbl_employee_technology refers(foreign key) to employeeid of the master table tbl_employees.

ALTER TABLE [dbo].[tbl_employee_technology]  WITH CHECK ADD  CONSTRAINT [FK_tbl_employee_technology_tbl_employees] FOREIGN KEY([employeeid])
REFERENCES [dbo].[tbl_employees] ([employeeid])
GO

ALTER TABLE [dbo].[tbl_employee_technology] CHECK CONSTRAINT [FK_tbl_employee_technology_tbl_employees]
GO


Here, technologyid of the table tbl_employee_technology refers(foreign key) to technologyid of the master table tbl_technology.

 ALTER TABLE [dbo].[tbl_employee_technology]  WITH CHECK ADD  CONSTRAINT [FK_tbl_employee_technology_tbl_technology] FOREIGN KEY([technologyid]) REFERENCES [dbo].[tbl_technology] ([technologyid])
GO

ALTER TABLE [dbo].[tbl_employee_technology] CHECK CONSTRAINT [FK_tbl_employee_technology_tbl_technology]


Again to store the various technologies known to an employee, as shown in the above figure,  we use the following insert command for the table  tbl_employee_technology.

Insert into tbl_employee_technology(employeeid,technologyid)
select 1,6
union all
select 1,5
union all
select 1,1
union all
select 1,8
union all
select 1,3
union all
select 2,3
union all
select 2,8
union all
select 2,4
union all
select 3,3
union all
select 3,8
union all
select 3,4
union all
select 4,5
union all
select 4,2
union all
select 4,4
union all
select 5,5
union all
select 5,2
union all
select 5,4
union all
select 6,1
union all
select 6,3
union all
select 6,4


Here , since one employee can have the knowledge of multiple technologies, so we insert one row for each technology known by the employee. For example, since the employee "Vivek Johari" knows 5 technologies, so insert 5 rows for this employee with one row per technology. 


Project Technology relationship


An Enterprise application , works on multiple technologies like they use asp.net with c#/vb.net for server side front end coding, java script, css, jquery for client side coding and SQL server/ Oracle/ My SQL for storing the database. So in this way, a project can include multiple technologies. Similarly, multiple projects can work on asp.net or java script or css. So there is many to many relationship between projects and technologies. So we need a transaction table to store that relationship.

CREATE TABLE [dbo].[tbl_project_technology](
    [projecttechnologyid] [int] IDENTITY(1,1) NOT NULL,
    [projectid] [int] NULL,
    [technologyid] [int] NULL
) ON [PRIMARY]

GO


Foreign Key relationship with their master table is given below:-

Here, projectid of the table tbl_project_technology refers(foreign key) to projectid of the master table tbl_project.

 
ALTER TABLE [dbo].[tbl_project_technology]  WITH CHECK ADD  CONSTRAINT [FK_tbl_project_technology_tbl_project] FOREIGN KEY([projectid])
REFERENCES [dbo].[tbl_project] ([projectid])
GO

ALTER TABLE [dbo].[tbl_project_technology] CHECK CONSTRAINT [FK_tbl_project_technology_tbl_project]
GO

Here, technologyid of the table tbl_project_technology refers(foreign key) to technologyid of the master table tbl_technology.

ALTER TABLE [dbo].[tbl_project_technology]  WITH CHECK ADD  CONSTRAINT [FK_tbl_project_technology_tbl_technology] FOREIGN KEY([technologyid])
REFERENCES [dbo].[tbl_technology] ([technologyid])
GO

ALTER TABLE [dbo].[tbl_project_technology] CHECK CONSTRAINT [FK_tbl_project_technology_tbl_technology]
GO





Again to store the various technologies used to a project, as shown in the above figure, we use the following insert command for the table tbl_project_technology. Here instead of storing the project name and technology name , we use their id taken from there respective master table.

Insert into tbl_project_technology(projectid,technologyid)
Select 1, 8
union all
select 1, 4
union all 
select 1, 3
union all
select 2, 2
union all
select 2,5
union all
select 2,4
union all
select 2,3
union all
select 3,1
union all
select 3,6
union all
select 3,4
union all
select 3,8


Here also, we used technologyid from the table tbl_technology and projectid from the table tbl_project. In this table too, if a project involves multiple technologies, we store the information about each technology for a project in a separate row.

With the help of this question, I tried to explain how to design a database tables and their relationship for a given situation. If anyone has any query regarding this, you can mail me at askvivekjohari@gmail.com. 
Waiting for your valuable feedback :-)


DMCA.com

Monday, January 6, 2014

Answer - Objective Question (Which table is called Heap table in SQL Server)

Question:-

   Which table is called Heap table in SQL Server.

1) Table without any indexes

2) Table without any clustered index (Correct Answer)

3) Table without any non clustered Index

4) None of the above


Many people attempted this question and mostly gives the correct answer. The correct answer is option 2 ( Table without any clustered index). Heap table in SQL Server is defined as the table which do not have any clustered index defined on it.

Thanks to all who attempted this question.
DMCA.com

Thursday, January 2, 2014

Question of the Week - 2/1/2014 - Question 1

Question :-

Suppose we have to design a database in which employees can work on multiple projects.There are many designation defined in the organization to which each employee can belongs.
 For example, Software engineer, Team lead, Project Manager, Project lead, QC engineer, QC Lead, QC manager etc. An employee can belong to one designation at a time.
 It means at a time an employee can either be Software engineer or team lead or project manager but not software engineer as well team lead at a same time. 

Also the projects can work on different technologies like .Net, SQL Server, Oracle, HTML5 etc. Every project has a start data and end date. A project can work can include many technologies like .net, SQL Server , Java script, HTML5 etc. Most of the time every project work include a front end server technology like JAVA. Asp.net, uses Java script, HTML, CSS as client side technologies and SQL Server, oracle as back end database server. So In this ways a project work can include many technologies as in above example,  suppose a project can use Asp.net as server end front end technology, java script, HTML, CSS, AJAX as client side technology and SQL server as back end database server.


What should be the design the database schema (tables, their foreign keys, primary keys etc).

DMCA.com

Wednesday, January 1, 2014

Answer - Objective Question (Number of non clustered indexes possible on a Table in SQL Server 2008R2)

Question :- 

How many non clustered indexes are possible in a table in SQL Server 2008R2


1)  1

2) 249

3) 999  (Correct answer)

4) None of these


Explanation


 21 people gives answer to this Question and most of them is correct.

Correct answer is 999 (Option 4).

In SQL Server 2008 onwards, we can create 999 non clustered indexes and 1 clustered Index.

In SQL Server 2005 We can create 249 non clustered Indexes and 1 clustered Index.

Related Article




DMCA.com

Answer - Question of the Week - 28/12/2013 - Question 2

Question:- Suppose we have a table named tbl_charactername have only one column say "charactername".
It contains 10 rows. First 5 rows contains "Vivek Johari" and the next 5 rows contains "Abhinav Golwalkar"

tbl_charactername
---------------------
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar

Now we need a SQL Query which can return the result in the form

Desired Result:-

Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar

Query should work even if the table has 30 rows with first 15 contains "vivek johari" and next 15 contains "Abhinav Golwalkar" or 40 rows with first 20 contains "vivek johari" and next 20 contains "Abhinav Golwalkar" etc. But output should contains "Vivek Johari" and "Abhinav Golwalkar" in alternate rows.

Keep Answering.......:-)

Answer:-


We have got three method to solve this problem.Thanks to Aviral Lamba and Sean Senneka for giving the correct answer.

Answer given by Aviral lamba

SELECT CASE WHEN (A.ROW1 % 2)=0 THEN 'Vivek Johari' WHEN (A.ROW1 % 2)=1 THEN 'Abhinav Golwalkar' end PERSONNAME FROM(
SELECT charactername,ROW_NUMBER()OVER (ORDER BY charactername)AS ROW1 FROM tbl_charactername )A

Answer Given by Sean Senneka

Answer 1 :-

SELECT charactername
, ROW_NUMBER() OVER (PARTITION BY charactername ORDER BY charactername) ordby
FROM tbl_charactername
ORDER BY 2,1

Answer 2:-

SELECT charactername
FROM tbl_charactername
ORDER BY ROW_NUMBER() OVER (PARTITION BY charactername ORDER BY charactername) ASC,charactername DESC

For me the second answer given by Sean is the best as it neither use any hardcore value and also gives the desired column.



DMCA.com

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