CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduresreporting-servicessql-server-2008-r2
xorpower
xorpower

Reputation: 18963

Stored procedure doesn't show result

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

Below is the SP given

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] IN ( @ProjHomeGrp ) )
      AND ( TimeByDay <= @EndDate )
      AND ( ActualWork > 0 )
      AND ( [Project Hierarchy] IN ( @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!

Upvotes: 1

Views: 3338

Answers (3)

user3598613
user3598613

Reputation: 1

Create procedure like:

CREATE PROCEDURE Show_Sproceduresome
   AS 
 Begin
    select points,coursename,c.type from OfferType o,Courses c,Candidate cd, CourseOffer
     where name = 'Francisco' 
     and o.OfferId = co.OfferId 
     and co.CourseId = c.CourseId 
     and co.UCASNumber = cd.UCASNumber
 End

Execute the procedure like:

exec Sproceduresome

Does not display results or out put

Upvotes: 0

David Brabant
David Brabant

Reputation: 43489

As Oded said, table valued parameters are the way to go. However, here is a solution based on dynamic sql, with all the problems that might imply...

create procedure [dbo].[sp_tst_CSENG_JulieCapitalHours]
        @StartDate datetime ,
        @enddate datetime ,
        @ProjHomeGrp nvarchar(MAX) ,
        @ProjHier nvarchar(MAX)
    as  
begin
    declare @sql nvarchar(max)
    declare @paramDefs nvarchar(max)

    select @sql = N'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 (' + @ProjHomeGrp + ') )
            and ( TimeByDay <= @EndDate) )
            and ( ActualWork > 0 )
            and ( [Project Hierarchy] IN ( ' + @ProjHier + ' ) )
    order by ProjectName, ResourceName'

    select @paramDefs = N'@StartDate datetime, @EndDate datetime'

    exec sp_executesql @sql, @paramDefs, @StartDate = @StartDate, @EndDate =  @EndDate
end

Upvotes: 0

Oded
Oded

Reputation: 498972

This looks wrong:

[Project Home Group] IN ( @ProjHomeGrp )

You can't use IN with an NVARCHAR type and expect to be able to use set based operations on it directly.

This may be closer to what you are looking for:

[Project Home Group] = @ProjHomeGrp

And (guessing here, from your use of IN) if you are using the NVARCHAR(MAX) parameters as delimited text (say integers with commas as separators), there are better ways - you should be using Table Valued Parameters for such thing.

Upvotes: 1

Related Questions