Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
to clarify - you are passing in a list like PHG1,PHG2,PHG3,PHG4,PHG5,PHG6,PHG7 and only getting results like PHG1,PHG2,PHG3,PHG4,PHG5? –  JHFB May 8 '12 at 14:23
    
I am getting lesser records then expected in the result set. It looks the values in the list are getting terminated when executing the SP. –  Romil N May 8 '12 at 14:33
1  
Then I would suspect your varaible is too small to contain the whole list. Not necesarrily the one in the proc, but where ever teh list is generated from in the application. Use profiler to see what is being sent to the stored proc. –  HLGEM May 8 '12 at 14:52
1  
If you use the split function referenced in the other answer the split is truncating your valuelist to varchar(512). –  Mikael Eriksson May 9 '12 at 8:43
1  
See my answers here for different split function alternatives : stackoverflow.com/questions/7274514/sql-query-to-match-keywords stackoverflow.com/questions/10182533/… –  Aaron Bertrand May 9 '12 at 11:03
show 8 more comments

1 Answer

up vote 0 down vote accepted

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
share|improve this answer
    
This surely brings more record than the previous function did.But it doesn't bring all records :-( –  Romil N May 9 '12 at 10:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.