存储过程中的“select”语句无效

时间:2022-07-02 16:38:51

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:

会儿,印刷:

存储过程中的“select”语句无效

No values returned or Selected:

没有返回或选择的值:

存储过程中的“select”语句无效

1 个解决方案

#1


4  

Your initial declaration settings only select data from your TimeLog table, which clearly contains data. Because you are then inner joining 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 joins 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 joining 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 joins 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