Click here to Skip to main content
16,019,873 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
USE [HRRecruitment]
GO
/****** Object:  StoredProcedure [dbo].[GenerateReport]    Script Date: 04/05/2015 21:30:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GenerateReport]
(
@StartDate DATE,
 @EndDate DATE,
 @Email nvarchar(40)
 )
as
begin
Declare @start VarChar, @end VarChar

If not exists(select * from tblWorkExpDetails where Email=@Email)
begin	
select 
pd.FirstName,
pd.LastName,
pd.Mobile,
pd.Email,
pd.Sources,
ed.XthPercentage,
ed.XIIthPercentage,
ed.GPercentage,
ed.PGPercentage,
wed.CurrentSalary,
wed.TotalExperience,
fd.Typing
from
tblPersonalDetails pd 
Inner Join tblEducationalDetails ed on pd.Email=ed.Email
Inner Join tblWorkExpDetails wed on pd.Email=wed.Email
Inner Join tblFresherDetails fd on pd.Email=fd.Email
where pd.JoinDate Between @StartDate and @EndDate
end
else if not exists(select * from tblFresherDetails where Email=@Email)
begin
select 
pd.FirstName,
pd.LastName,
pd.Mobile,
pd.Email,
pd.Sources,
ed.XthPercentage,
ed.XIIthPercentage,
ed.GPercentage,
ed.PGPercentage,
wed.CurrentSalary,
wed.TotalExperience,
fd.Typing
from
tblPersonalDetails pd 
Inner Join tblEducationalDetails ed on pd.Email=ed.Email
Inner Join tblWorkExpDetails wed on pd.Email=wed.Email
Inner Join tblFresherDetails fd on pd.Email=fd.Email
where pd.JoinDate Between @StartDate and @EndDate
end
else
begin
select 
pd.FirstName,
pd.LastName,
pd.Mobile,
pd.Email,
pd.Sources,
ed.XthPercentage,
ed.XIIthPercentage,
ed.GPercentage,
ed.PGPercentage,
wed.CurrentSalary,
wed.TotalExperience,
fd.Typing
from
tblPersonalDetails pd 
Inner Join tblEducationalDetails ed on pd.Email=ed.Email
Inner Join tblWorkExpDetails wed on pd.Email=wed.Email
Inner Join tblFresherDetails fd on pd.Email=fd.Email
where pd.JoinDate Between @StartDate and @EndDate
end
end;
Posted
Updated 5-Apr-15 7:47am
v2
Comments
Maciej Los 5-Apr-15 13:47pm    
What's the question?

1 solution

Use "left join" rather than "inner join" and you'll get data from both sides of the join regardless rather than when they match. That way if the table is empty you'll get "null"s back rather than no rows at all.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900