I have tried implementing a stored procedure for displaying the result from different tables by using inner join but my problem is select statement is not returning any result but its printing some of the values as messages.
我尝试实现一个存储过程,通过使用内连接来显示不同表的结果,但我的问题是select语句不返回任何结果,而是将一些值打印为消息。
alter proc EmployeeReport(@empid int)
as
begin
declare @inTime time(0)
declare @outTime time(0)
declare @fromDate date
declare @toDate date
set @inTime = (select CAST(InTime as time(0)) from Timelog where EmployeeId=@empid)
set @outTime = (select CAST(OutTime as time(0)) from Timelog where EmployeeId = @empid)
set @fromDate = (select cast (InTime as date) from Timelog where EmployeeId= @empid)
set @toDate = (select cast (outTime as date) from Timelog where EmployeeId= @empid)
select @fromDate as FromDate
,@toDate as ToDate
,c.Name as Client
,p.Name as Project
,@inTime as InTime
,@outTime as OutTime
,t.TotalTime
from Timelog t
inner join Employee e
on e.id = t.EmployeeId
inner join Project p
on p.Id = t.EmployeeProjectId
inner join Client c
on c.Id = p.ClientId
where t.EmployeeId = @empid
print @inTime
print @outTime
print @fromDate
print @toDate
end
I am attaching the output files what i am getting , please help me with this
我正在附上我得到的输出文件,请帮忙
Messeges getting printed:
会儿,印刷:
No values returned or Selected:
没有返回或选择的值:
1 个解决方案
#1
4
Your initial declaration settings only select
data from your TimeLog
table, which clearly contains data. Because you are then inner join
ing from here to other tables, if those other tables have no data, nothing will be returned.
您的初始声明设置只从TimeLog表中选择数据,它显然包含数据。因为您正在从这里到其他表进行内部连接,如果其他表没有数据,则不会返回任何内容。
Either make sure that your Employee
, Project
and Client
tables have data in them or change your join
s to left
instead of inner
:
要么确保您的员工、项目和客户端表中包含数据,要么将连接从内部改为左:
select @fromDate as FromDate
,@toDate as ToDate
,c.Name as Client
,p.Name as Project
,@inTime as InTime
,@outTime as OutTime
,t.TotalTime
from Timelog t
left join Employee e
on e.id = t.EmployeeId
left join Project p
on p.Id = t.EmployeeProjectId
left join Client c
on c.Id = p.ClientId
#1
4
Your initial declaration settings only select
data from your TimeLog
table, which clearly contains data. Because you are then inner join
ing from here to other tables, if those other tables have no data, nothing will be returned.
您的初始声明设置只从TimeLog表中选择数据,它显然包含数据。因为您正在从这里到其他表进行内部连接,如果其他表没有数据,则不会返回任何内容。
Either make sure that your Employee
, Project
and Client
tables have data in them or change your join
s to left
instead of inner
:
要么确保您的员工、项目和客户端表中包含数据,要么将连接从内部改为左:
select @fromDate as FromDate
,@toDate as ToDate
,c.Name as Client
,p.Name as Project
,@inTime as InTime
,@outTime as OutTime
,t.TotalTime
from Timelog t
left join Employee e
on e.id = t.EmployeeId
left join Project p
on p.Id = t.EmployeeProjectId
left join Client c
on c.Id = p.ClientId