hi all,
in my database i am using one stored procedure for getting all the leaves of the employees.
when i am right click on that stored procedure---> Excecute procedure----->giving the required values---->then ok
it is exceuting and showing as 1 row(s) affected but that particular row is not showing in output.
can anyone please help me on this issue and what may be the problem why this occurs?
my storedprocedure it is very big please don't mind
ALTER PROCEDURE [dbo].[SPGetEmployeeLeavesReportforall]
-- Add the parameters for the stored procedure here
@EmployeeId varchar(50),
@StartDate datetime,
@EndDate datetime,
@LeaveType int,
@SupervisorId varchar(20),
@StatusId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
if(@EmployeeId = 'All')
begin
if(@LeaveType = 0)
begin
if(@StartDate = '01/01/1753' and @EndDate = '01/01/1753')
begin
if(@StatusId = 0)
begin
select l.AppliedDate,l.ApprovedDate,l.ReasonForLEave,e.FullName,a.Status ,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy
from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId
and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
end
else
begin
select l.AppliedDate,l.ApprovedDate,l.ReasonForLEave,e.FullName,a.Status ,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy
from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.Status = @StatusId
and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
end
end
else
begin
if(@StatusId = 0)
begin
select e.FullName,a.Status ,l.ReasonForLEave,l.AppliedDate,l.ApprovedDate,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy
from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.FromDate >=@StartDate and l.ToDate <=@EndDate
and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId
and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
end
else
begin
select e.FullName,a.Status ,l.ReasonForLEave,l.AppliedDate,l.ApprovedDate,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy
from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.FromDate >=@StartDate and l.ToDate <=@EndDate
and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.Status = @StatusId
and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
end
end
end
else
begin
if(@StartDate = '01/01/1753' and @EndDate = '01/01/1753')
begin
if(@StatusId = 0)
begin
select e.FullName,l.AppliedDate,l.ReasonForLEave,l.ApprovedDate,a.Status ,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType and
l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
end
else
begin
select e.FullName,l.AppliedDate,l.ReasonForLEave,l.ApprovedDate,a.Status ,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType and l.Status = @StatusId and
l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
end
end
else
begin
if(@StatusId = 0)
begin
select e.FullName,a.Status ,l.AppliedDate,l.ReasonForLEave,l.ApprovedDate,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType
and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
end
else
begin
select e.FullName,a.Status ,l.AppliedDate,l.ReasonForLEave,l.ApprovedDate,l.FromDate,l.ToDate,l.NoofHrs,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType
and l.Status = @StatusId and l.EmployeeID in (select EmployeeID from Employee)and (e.FacultyFlag=0 or e.FacultyFlag=1) order by FromDate desc
end
end
end
end
else
begin
if(@LeaveType = 0)
begin
if(@StartDate = '01/01/1753' and @EndDate = '01/01/1753')
begin
if(@StatusId = 0)
begin
select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.AppliedDate,l.ApprovedDate,l.ToDate,l.NoofHrs,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.EmployeeID = @EmployeeId and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId order by FromDate desc
end
else
begin
select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.AppliedDate,l.ApprovedDate,l.ToDate,l.NoofHrs,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.EmployeeID = @EmployeeId and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.Status = @StatusId order by FromDate desc
end
end
else
begin
if(@StatusId = 0)
begin
select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.EmployeeID = @EmployeeId and l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId
order by FromDate desc
end
else
begin
select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.EmployeeID = @EmployeeId and l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.Status=@StatusId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId
order by FromDate desc
end
end
end
else
begin
if(@StartDate = '01/01/1753' and @EndDate = '01/01/1753')
begin
if(@StatusId = 0)
begin
select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.EmployeeID = @EmployeeId and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType
order by FromDate desc
end
else
begin
select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.EmployeeID = @EmployeeId and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.Status=@StatusId and l.LeaveTypeId = @LeaveType
order by FromDate desc
end
end
else
begin
if(@StatusId = 0)
begin
select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.EmployeeID = @EmployeeId and l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType
order by FromDate desc
end
else
begin
select e.FullName,a.Status ,l.FromDate,l.ReasonForLEave,l.ToDate,l.ApprovedDate,l.NoofHrs,l.AppliedDate,lm.LeaveType,
(select FullName from Employee where EmployeeID = l.Approvedby)As ApprovedBy from Leave l,LeaveType_Master lm,Employee e,ApprovalStatus_Master a
where l.EmployeeID = @EmployeeId and l.FromDate >=@StartDate and l.ToDate <=@EndDate and l.LeaveTypeId = lm.LeaveTypeId and l.Status=@StatusId and l.EmployeeID = e.EmployeeID and l.Status = a.StatusId and l.LeaveTypeId = @LeaveType
order by FromDate desc
end
end
end
end
END