Link to home
Start Free TrialLog in
Avatar of u2envy1
u2envy1

asked on

Passing multi-value parameter in stored procedure

How do I pass a multi-Value parameter into a stored procedure with reporting services 2008


ALTER PROCEDURE [dbo].[MyProc] 
@ID bigint
SELECT * FROM Table1
WHERE (ID IN (@ID))

Open in new window

Avatar of u2envy1
u2envy1

ASKER

My stored procedure & function works fine. Tested it in Reporting Services 2008. When I try to run the report I get the following error.....

Procedure procMultiPayrollSummaryReport has too many arguments specified
SP .....
ALTER PROCEDURE [dbo].[procMultiPayrollSummaryReport] 
	-- Add the parameters for the stored procedure here
	@FromDate DateTime,
	@ToDate DateTime,
	@EndDate DateTime,
	@Department varchar(100),
	@Division varchar(100),
	@CostCentre varchar(100),
	@Group varchar(100),
	@Payperiod varchar(100),	
	@EmployeeNumber varchar(100),
	@EmployeeName varchar(100),
	@EmployeeSurname varchar(100)
	
	
AS
 
 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	-- Temp Table
	declare @periods table (fromdate datetime, todate datetime)
	declare @idate datetime
	declare @tdate datetime
	declare @DateDiff int	
 
	set @idate = @FromDate
	set @tdate = @ToDate
	set @DateDiff = DATEDIFF(d, @FromDate, @ToDate)	
 
	while @tdate <= @EndDate
	begin
			insert @periods values(@iDate, @tDate)
			set @idate = dateadd(d, datediff(d, @iDate, @tdate), @idate)
			set @tdate = dateadd(d, datediff(d, @iDate, @tdate), @tdate)
			set @tdate = DATEADD(d,@DateDiff,@tdate)
			
	end
 
	--select * from @periods;
    -- Insert statements for procedure here
    SELECT  
        Convert(nvarchar,cast(e.fromdate as nvarchar(10)) + ' to ' + cast(e.todate as nvarchar(10))) AS 'RangeDate',
        a.EmployeeNumber, 
        a.EmployeeName, 
        a.Surname, 
        d.TimeCategoryName,
        c.Hours,
        c.Minutes,
        b.date
	FROM Employee a
	INNER JOIN DailyHours b ON a.EmployeeID = b.EmployeeID 
	INNER JOIN DailyHoursTimeCategories c ON b.DailyHoursID = c.DailyHoursID 
	INNER JOIN TimeCategory d ON c.TimeCategoryID = d.TimeCategoryID
	INNER JOIN @periods e on b.date between e.fromdate and e.todate
	WHERE     a.DepartmentID IN (select Param from dbo.fn_SplitParam(@Department,',')) AND a.DivisionID IN (select Param from dbo.fn_SplitParam( @Division,',')) 
    AND a.CostCentreID IN (select Param from dbo.fn_SplitParam(@CostCentre,',')) AND a.GroupID IN (select Param from dbo.fn_SplitParam(@Group,',')) AND a.PayperiodID IN (select Param from dbo.fn_SplitParam(@Payperiod,',')) AND 
                      (a.EmployeeNumber LIKE @EmployeeNumber + '%') AND (a.EmployeeName LIKE @EmployeeName + '%') AND 
                      (a.Surname LIKE @EmployeeSurname + '%')
                      
                      
    -- ChargeServiceStartDate IN (select Param from fn_SplitParam(@ServiceYear,','))             
    --                  WHERE     (a.DepartmentID = @Department) AND (a.DivisionID = @Division) 
    --AND (a.CostCentreID = @CostCentre) AND (a.GroupID = @Group) AND (a.PayperiodID = @Payperiod ) AND 
    --                  (a.EmployeeNumber LIKE @EmployeeNumber + '%') AND (a.EmployeeName LIKE @EmployeeName + '%') AND 
    --                  (a.Surname LIKE @EmployeeSurname + '%')
                      
                      
--USE VisionEnterprise_db;
--GO
--Execute [dbo].[procMultiPayrollSummaryReport] '2009-01-25 00:00:00.000',
-- '2009/02/25 00:00:00.000',
--  '2009/04/25 00:00:00.000',
--   4,
--    7,
--      4,
--        5,
--         17,
--          23,
--           Eric,
--            Africa;
--GO
    
 	
END
 
FUNCTION
 
ALTER FUNCTION dbo.fn_SplitParam(@RepParam nvarchar(4000), @Delim char(1)= ',')
	RETURNS @Values TABLE (Param nvarchar(4000))AS
	BEGIN
	DECLARE @chrind INT
	DECLARE @Piece nvarchar(4000)
	SELECT @chrind = 1
	WHILE @chrind > 0
	BEGIN
	SELECT @chrind = CHARINDEX(@Delim,@RepParam)
	IF @chrind > 0
	SELECT @Piece = LEFT(@RepParam,@chrind - 1)
	ELSE
	SELECT @Piece = @RepParam
	INSERT @Values(Param) VALUES(@Piece)
	SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
	IF LEN(@RepParam) = 0 BREAK	
END	
	RETURN
	
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of u2envy1
u2envy1

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial