SSRS Pass Multi-Value Parameter to Stored Procedure

I was able to get some help with passing multi-value parameters from an SSRS report to a SP several months back, but now I have a follow up.

@StoreID is working fine but @GroupName gives me the following error 'Conversion failed when converting the varchar value 'Redemption' to data type int'

I'm using the same function for both and have never had any issues with other SP. I'm must be doing something wrong with the @GroupName.

ALTER PROCEDURE [dbo].[sp_GameScoring]

@BeginDate DateTime
,@EndDate DateTime
,@StoreID  varchar (1000)
, @GroupName varchar (1000)


AS




WITH GrossCollectedRank AS
	(
SELECT			ROW_NUMBER() OVER (ORDER BY SUM(GrossCollected)DESC) AS GrossCollectedRank
				, StoreID
				, StoreGameID
				, SUM(GrossCollected) AS GrossCollected
				,(CASE
						WHEN (SUM(CreditPlays) + SUM(BonusPlays) + SUM(CourtesyPlays)) = 0 
						THEN (GamePrice) 
						ELSE SUM(MoneyIN) /(SUM(CreditPlays) + SUM(BonusPlays) + SUM(CourtesyPlays)) 
						END) AS AvgPrice
				, Sum(Tickets) / Sum(GrossCollected) / 100 AS Payout

FROM            View_GrossGameSalesDetail

WHERE			StoreID IN (SELECT CAST(value as int) from dbo.ParmsToList(@StoreID,','))
				AND GroupName IN (SELECT CAST(value as int) from dbo.ParmsToList(@GroupName,','))
				AND (TransactionDate BETWEEN @BeginDate AND @EndDate)

GROUP BY		StoreID, StoreGameID, GamePrice
)
	


	SELECT	SM.StoreID
			, SM.StoreGameID
			, SM.GroupName
			, SM. MachineName
			, SM.ActiveDate
			, GC.GrossCollected
			, GC.GrossCollectedRank
			, GC.AvgPrice
			, GC.Payout

			
	FROM StoreMasterGameList SM
			JOIN GrossCollectedRank GC ON SM.StoreID = GC.StoreID and SM.StoreGameID = GC.StoreGameID
			

Open in new window

dk04Asked:
Who is Participating?
 
John_VidmarConnect With a Mentor Commented:
Cool, so instead of attempting to convert to int, convert to varchar(100):
AND GroupName IN (SELECT CAST(value as varchar(100)) from dbo.ParmsToList(@GroupName,','))

Open in new window

0
 
John_VidmarCommented:
Line 30, below, is attempting to convert each element in the comma-delimited list contained in @GroupName into integer values.  Instead of a number, I suspect @GroupName contains the value Redemption.
AND GroupName IN (SELECT CAST(value as int) from dbo.ParmsToList(@GroupName,','))

Open in new window

0
 
dk04Author Commented:
Yes it does. It is one of the groups to choose from. In the table it is a varchar (100)
0
 
dk04Author Commented:
It worked. Awesome! Thanks
0
 
dk04Author Commented:
Perfect, just what I needed.
0
All Courses

From novice to tech pro — start learning today.