I have created a stored procedure (to be used in SSRS) that has parameters whose values are more than one. When I execute this stored procedure in SSMS by providing the values for that parameter, SP doesn't return any result. It should return more than 1 rows
CREATE PROCEDURE [dbo].[sp_tst_CSENG_JulieCapitalHours]
@StartDate DATETIME ,
@EndDate DATETIME ,
@ProjHomeGrp NVARCHAR(MAX) ,
@ProjHier NVARCHAR(MAX)
AS
BEGIN
SELECT [Capital Project] ,
[Capital Task] ,
ResourceName ,
ProjectName ,
[Project Home Group] ,
ActualWork ,
TimeByDay ,
ResourceStandardRate ,
ActualWork * ResourceStandardRate AS Dollars ,
[Project Hierarchy]
FROM [IR.CapOnly]
WHERE ( TimeByDay >= @StartDate )
AND ( [Project Home Group] = ( @ProjHomeGrp ) )
AND ( TimeByDay <= @EndDate )
AND ( ActualWork > 0 )
AND ( [Project Hierarchy] = ( @ProjHier ) )
ORDER BY ProjectName ,
ResourceName
END
You can see that in the where clause, Project Home Group (@ProjHomeGrp) and Project Hierarchy (@ProjHier) are the parameters, whose value when supplied (more than 1) SP returns zero values.
The value that I'm passing are:
Start Date: 1/1/2011
End Date: 12/31/2012
@ProjHomeGrp : PHG1,PHG2,PHG3,PHG4,PHG5,PHG6,PHG7
@ProjHier: PROH1, PROH2, PROH3
Let me know for any questions!
Best Answer
You need to split your parameters into rows and use
where ... in
as a check.Using a split function like this one your code could look like this.