有关查询及其执行的怀疑 [英] Doubt regarding query and its exceution
本文介绍了有关查询及其执行的怀疑的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
大家好，
在我的数据库中，我正在使用一个存储过程来获取所有雇员的叶子.
当我右键单击该存储过程时--->执行程序--->给出所需的值->然后确定
它正在执行并显示为受影响的1行，但该特定行未显示在输出中.
任何人都可以在这个问题上为我提供帮助，为什么会发生这种情况呢?
我的存储过程很大，请不要介意
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
推荐答案
我认为没有select语句. Sp可能会更新或删除或插入行.
您可能将Temp表用于某些操作.可能会给该行带来影响的味精.
检查您的Sp
问候，
Pal
I think there is no select statement. Sp may update or delete or insert row.
You might be used Temp tables for some operations. it may give this row affected msg.
check your Sp
regards,
Pal
如果您的存储过程更新了数据库中的数据，您将获得更新多少行的信息.除非该过程还返回行(在过程中编码了一条select语句)，否则不会为该数据生成任何输出.
如果您的意思是从调用方执行过程时，则应获取受影响的行数，然后从ExecuteNonQuery中获取返回值并进行检查.
If your stored procedure updates the data in the database you''ll get info how many rows are updated. No output for the data is generated unless the procedure also returns rows (a select statement is coded in the procedure).
If you mean that when you execute the procedure from the calling side, then you should get the amount of affected rows then take the return value fromExecuteNonQueryand check that.
您还没有使用select查询时，sp中存在问题，因此请显示您可以从中帮助您提供适当解决方案的sp.
You have not used select query, there is problem in sp, so show your sp from which i can help for providing a proper solution.
这篇关于有关查询及其执行的怀疑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
