CODEWITHSUNDEEP
sql-server
Vj charan
Vj charan

Reputation: 21

"select" statement in stored Procedure is not working

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.

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:

Upvotes: 2

Views: 1722

Answers (1)

iamdave
iamdave

Reputation: 12243

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.

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

Upvotes: 4

Related Questions