Stored Procedure is not returning complete result

Question

I am using the query as mentioned in the answer accepted in the question located at Stored Procedure doesn't show result in SQL Server

Now when I run this SP with the values provided in the parameter, the SP shows results only of specific parameter values when returning the result. Can there be any chance of values getting terminated? The parameter values are multiple and comma seperated.

Below is the SP;

    ALTER PROCEDURE [dbo].[sp_tst_JulieExportCSENGOnly]
    @ProjectHierarchy NVARCHAR(max),
    @ProjectHomeGroup NVARCHAR(max),
    @ProjName NVARCHAR(max),
    @ProjOwn NVARCHAR(max),
    @TimeByDay DATETIME,
    @TimeByDay2 DATETIME
AS 
   SET NOCOUNT ON

   BEGIN

   DECLARE @ProjectHierarchyTbl TABLE ( Value NVARCHAR(MAX))
   DECLARE @ProjectHomeGroupTbl TABLE ( Value NVARCHAR(MAX))
   DECLARE @ProjNameTbl TABLE ( Value NVARCHAR(MAX))
   DECLARE @ProjOwnTbl TABLE ( Value NVARCHAR(MAX))

    INSERT  INTO @ProjectHierarchyTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjectHierarchy) ; 

   INSERT  INTO @ProjectHomeGroupTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjectHomeGroup) ; 


    INSERT  INTO @ProjNameTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjName) ; 

    INSERT  INTO @ProjOwnTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjOwn) ;  

   SELECT
  [IR.TimesheetProjectHoursByDay].[Project Departments]
  ,[IR.TimesheetProjectHoursByDay].ProjectName
  ,[IR.TimesheetProjectHoursByDay].ProjectOwnerName
  ,[IR.TimesheetProjectHoursByDay].[Project Hierarchy]
  ,[IR.TimesheetProjectHoursByDay].[Project Home Group]
  ,[IR.TimesheetProjectHoursByDay].[CSENG Project Type]
  ,[IR.TimesheetProjectHoursByDay].TaskName
  ,[IR.TimesheetProjectHoursByDay].[Capital Project]
  ,[IR.TimesheetProjectHoursByDay].[Capital Task]
  ,[IR.TimesheetProjectHoursByDay].TimeByDay
  ,[IR.TimesheetProjectHoursByDay].ResourceName
  ,[IR.TimesheetProjectHoursByDay].ActualWork
  ,[IR.TimesheetProjectHoursByDay].[Resource Home Group]
  ,[IR.TimesheetProjectHoursByDay].[Resource Cost Center]
  ,[ResourceStandardRate]
FROM
  [IR.TimesheetProjectHoursByDay]
WHERE
  [IR.TimesheetProjectHoursByDay].[Project Departments] LIKE N'CSENG'
  AND ActualWork > 0
  AND [IR.TimesheetProjectHoursByDay].[Project Hierarchy] IN (SELECT VALUE FROM @ProjectHierarchyTbl)
  AND [IR.TimesheetProjectHoursByDay].[Project Home Group] IN (SELECT VALUE FROM @ProjectHomeGroupTbl)
  AND ProjectName IN (SELECT VALUE FROM @ProjNameTbl)
  AND ProjectOwnerName IN (SELECT VALUE FROM @ProjOwnTbl)
  AND [IR.TimesheetProjectHoursByDay].TimeByDay >= @TimeByDay
  AND [IR.TimesheetProjectHoursByDay].TimeByDay <= @TimeByDay2

   END 

Thanks!

Answer 1

The split function you use truncate the valuelist to varchar(512). There are several split functions to choose from in this answer and here is another.

create function dbo.Split(@sep char(1), @s varchar(max))
returns @T table(s varchar(max))
as
begin
  while len(@s) > 0
  begin
    insert into @T values(left(@s, charindex(@sep, @s+@sep)-1))
    set @s = stuff(@s, 1, charindex(@sep, @s+@sep), '')
  end
  return
end