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 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!

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

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.

CREATE PROCEDURE [dbo].[sp_tst_CSENG_JulieCapitalHours]
    @StartDate DATETIME ,
    @EndDate DATETIME ,
    @ProjHomeGrp NVARCHAR(MAX) ,
    @ProjHier NVARCHAR(MAX)
AS 
BEGIN

    DECLARE @ProjHomeGrpTbl TABLE (Value NVARCHAR(4))

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

    DECLARE @ProjHierTbl TABLE (Value NVARCHAR(5))

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

    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] in (SELECT Value FROM @ProjHomeGrpTbl) )
            AND ( TimeByDay <= @EndDate )
            AND ( ActualWork > 0 )
            AND ( [Project Hierarchy] in (SELECT Value FROM @ProjHierTbl) )
    ORDER BY ProjectName ,
            ResourceName

END 
share|improve this answer
    
I have updated your comment with my inputs. –  Romil N May 7 '12 at 12:03
    
String or binary data would be truncated. You need to adapt the field size of the table variables to whatever sizes you need. I choose 4and 5 based on the data you provided in your question. It should be the same sizes as [Project Home Group] and [Project Hierarchy]. –  Mikael Eriksson May 7 '12 at 12:10
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.