SSRS Pass Multi-Value Parameter to Stored Procedure

Hi,

Here is a code snippette that I'm creating a table of values from a csv list.

	declare 
		--@S varchar( max )
		--, 
		@Split char(1)
		, @X xml

	select 
		--@S = '1,2,3,4,5, 7',
		@Split = ','

	--select @X = convert(xml,'<root><s>' + replace( @CustomerIDs, @Split, '</s><s>' ) + '</s></root>' )
	select @X = convert(xml,'<root><s>' + replace( @CustomerStorerKeyIDs, @Split, '</s><s>' ) + '</s></root>' )

	if @debug > 0
		SELECT [Value] = T.c.value('.','int')
		FROM @X.nodes('/root/s') T(c)
	
	declare @StorerKey nvarchar( max )
	/*
	select @StorerKey = isnull( @StorerKey + N',', '' ) + N'''''' + StorerKey + N''''''
	from dbo.CustomerStorerKey
	where
		CustomerID --= @CustomerID
			in (
				select [Value] = T.c.value('.','int')
				from @X.nodes('/root/s') T(c)
				)
	;
	*/
	select @StorerKey = isnull( @StorerKey + N',', '' ) + N'''''' + StorerKey + N''''''
	from dbo.CustomerStorerKey
	where
		CustomerStorerKeyID --= @CustomerID
			in (
				select [Value] = T.c.value('.','int')
				from @X.nodes('/root/s') T(c)
				)
	;

Select allOpen in new window

     

HTH
  David

Thanks Guy,

I created the Function and adjusted the Stored Procedure. When I run the report and choose one item in the parameter it runs fine, but when I select two items in one of the parameters I get an error that says "Error converting data type nvarchar to int".

The reason for that conversion error is most likely the function that's returning nvarchar as opposed to int.

In addition to A3s excellent article, have a look at the following, it mentions a function that returns int: Reporting On Data From Stored Procedures (part 2)

Using that info perhaps you can convert the function you've currently got into one that returns int.  Or just use the one mentioned in my article, up to you.  After reading A3s article it should be clear that there are several different (sometimes very different) ways to achieve the same output.

ValentinoV: I received the same error. Here is my code for the Stored Procedure. @StoreID and @ItemStatus are the two parameters that could be multi-value. In the table StoreID is int and @ItemStatus is bit.

ALTER PROCEDURE [dbo].[sp_RedemptionAdjustmentAudit]

@BeginDate DateTime
,@EndDate DateTime
,@StoreID int 
,@ItemStatus bit 

AS

IF OBJECT_ID('tempdb..#TempRedemptionAdjustment') IS NOT NULL
/*Then it exists*/
   DROP TABLE #TempRedemptionAdjustment

SELECT        itmCode
INTO			#TempRedemptionAdjustment
FROM          RedemptionDataDetailByDay 
WHERE        (StoreID IN (@StoreID)) AND (TransactionDate BETWEEN @BeginDate AND @EndDate) AND (enable IN (@ItemStatus)) AND (adjusted <> 0)

SELECT			rdd.StoreID, rdd.TransactionDate, rdd.tckVal, rdd.itmCode, rdd.itmName, rdd.itmID, rdd.enable


FROM            RedemptionDataDetailByDay AS rdd 
                INNER JOIN #TempRedemptionAdjustment AS tt ON rdd.itmCode = tt.itmCode


WHERE      
			 (rdd.TransactionDate BETWEEN @BeginDate AND @EndDate) 
			 AND rdd.StoreID IN (select * from list_to_tbl(@StoreID))
			 AND rdd.enable IN (select * from list_to_tbl(@ItemStatus))
			 
ORDER BY		rdd.StoreID, rdd.itmCode, rdd.TransactionDate

Select allOpen in new window

if you take my function, all you have to do is to cast the value to numerical before using, like:
where yourfield in (select cast(value as int) from dbo.ParmsToList(@ItemStatus, ',')  )

Same error. Here is the Where clause:

WHERE  (rdd.TransactionDate BETWEEN @BeginDate AND @EndDate) 
            AND rdd.StoreID IN (SELECT CAST(VALUE AS INT) FROM dbo.ParmsToList(@StoreID,',') )
	 AND rdd.enable IN (SELECT CAST(VALUE AS INT) FROM dbo.ParmsToList(@ItemStatus,',') )

Select allOpen in new window

This is an issue:

,@StoreID int

If that's your multi-valued parameter, you need to define it as varchar(<length>) because it comes in as a comma-separated string (see my article for the full-blown details on that)...

yes, good catch :)

A3: ooh, thank you Sir! :)

ValentinoV: I read the article and I'm still having the same issue. I was able to get the AdventureWorks to work fine. I think the only difference is the datatype in the database. It would be the same if you were trying to accomplish the multi parameter to DepartmentID instead of Name in the HumanResources.Department table.

 If you need any further information let me know. The error is Conversion failed when converting varchar'1,2' to data type int.

In the database StoreID is Int. I have the SP for @StoreID set up as varchar (4) and in the report @StoreID has been set up as Text and Integer. Both ways I get the same error.

can you please show the current code of the stored proc?

As A3 said, please post the SP, without seeing what you've done we can't really help you solve that error...

I have the SP for @StoreID set up as varchar (4)

That seems a bit short...  Let's say you've got three stores selected in the report parameter.  A possible string that gets passed into the SP parameter is "3,11,23".  In this case that's seven characters.  To be sure use something like varchar(1000)...

Here is the SP. It is based on the function from ValentinoV

ALTER PROCEDURE [dbo].[sp_RedemptionAdjustmentAudit1]

@BeginDate DateTime
,@EndDate DateTime
,@StoreID varchar(1000) 
,@ItemStatus varchar(3)

AS

IF OBJECT_ID('tempdb..#TempRedemptionAdjustment') IS NOT NULL
/*Then it exists*/
   DROP TABLE #TempRedemptionAdjustment

SELECT        itmCode
INTO			#TempRedemptionAdjustment
FROM          RedemptionDataDetailByDay 
WHERE        (StoreID IN (@StoreID)) AND (TransactionDate BETWEEN @BeginDate AND @EndDate) AND (enable IN (@ItemStatus)) AND (adjusted <> 0)

SELECT			rdd.StoreID, rdd.TransactionDate, rdd.tckVal, rdd.itmCode, rdd.itmName, rdd.itmID, rdd.enable, rdd.orderquantity, rdd.unidadcasepack, rdd.stockminredemption, 
                rdd.stockmin, rdd.preciounidad, rdd.preciocosto, rdd.precioreal, rdd.InitialInventory, rdd.purchased, rdd.returned, rdd.redeemed, rdd.adjusted, rdd.trIn, rdd.trOut, 
                rdd.finalInventory, rdd.tckRedeemed, rdd.vInitialInventory, rdd.vPurchased, rdd.vReturned, rdd.vRedeemed, rdd.vAdjusted, rdd.vTrIn, rdd.vTrOut, rdd.vPriceChange, 
                rdd.vFinalInventory, r.descripcionrubro, r.category, st.StoreName, P.Name


FROM            RedemptionDataDetailByDay AS rdd 
				INNER JOIN Rubro AS r ON rdd.rubro = r.idrubro AND rdd.StoreID = r.StoreID 
				INNER JOIN Providers AS P ON rdd.ProviderID = P.ID AND rdd.StoreID = P.StoreID 
                INNER JOIN FiscalDatesByDay AS fd ON rdd.TransactionDate = fd.TransactionDate 
                INNER JOIN Stores AS st ON rdd.StoreID = st.StoreID
                INNER JOIN #TempRedemptionAdjustment AS tt ON rdd.itmCode = tt.itmCode


WHERE      
			 (rdd.TransactionDate BETWEEN @BeginDate AND @EndDate) 
			 AND rdd.StoreID IN (SELECT * FROM list_to_tbl(@StoreID)) 
			 AND rdd.enable IN (SELECT * FROM list_to_tbl(@ItemStatus)) 
			 
ORDER BY		rdd.StoreID, rdd.itmCode, rdd.TransactionDate

Select allOpen in new window

This one is based on Guy's article.

ALTER PROCEDURE [dbo].[sp_RedemptionAdjustmentAudit2]

@BeginDate DateTime
,@EndDate DateTime
,@StoreID  varchar (1000)
,@ItemStatus varchar (3)

AS

IF OBJECT_ID('tempdb..#TempRedemptionAdjustment') IS NOT NULL
/*Then it exists*/
   DROP TABLE #TempRedemptionAdjustment

SELECT        itmCode
INTO			#TempRedemptionAdjustment
FROM          RedemptionDataDetailByDay 
WHERE        (StoreID IN (@StoreID)) AND (TransactionDate BETWEEN @BeginDate AND @EndDate) AND (enable IN (@ItemStatus)) AND (adjusted <> 0)

SELECT			rdd.StoreID, rdd.TransactionDate, rdd.tckVal, rdd.itmCode, rdd.itmName, rdd.itmID, rdd.enable, rdd.orderquantity, rdd.unidadcasepack, rdd.stockminredemption, 
                rdd.stockmin, rdd.preciounidad, rdd.preciocosto, rdd.precioreal, rdd.InitialInventory, rdd.purchased, rdd.returned, rdd.redeemed, rdd.adjusted, rdd.trIn, rdd.trOut, 
                rdd.finalInventory, rdd.tckRedeemed, rdd.vInitialInventory, rdd.vPurchased, rdd.vReturned, rdd.vRedeemed, rdd.vAdjusted, rdd.vTrIn, rdd.vTrOut, rdd.vPriceChange, 
                rdd.vFinalInventory, r.descripcionrubro, r.category, st.StoreName, P.Name


FROM            RedemptionDataDetailByDay AS rdd 
				INNER JOIN Rubro AS r ON rdd.rubro = r.idrubro AND rdd.StoreID = r.StoreID 
				INNER JOIN Providers AS P ON rdd.ProviderID = P.ID AND rdd.StoreID = P.StoreID 
                INNER JOIN FiscalDatesByDay AS fd ON rdd.TransactionDate = fd.TransactionDate 
                INNER JOIN Stores AS st ON rdd.StoreID = st.StoreID
                INNER JOIN #TempRedemptionAdjustment AS tt ON rdd.itmCode = tt.itmCode


WHERE      
			 (rdd.TransactionDate BETWEEN @BeginDate AND @EndDate) 
			 AND rdd.StoreID IN (SELECT CAST(value as int) from dbo.ParmsToList(@StoreID,',')) 
			 AND rdd.enable IN (SELECT CAST(value as int) from dbo.ParmsToList(@ItemStatus,',')) 
			 
ORDER BY		rdd.StoreID, rdd.itmCode, rdd.TransactionDate

Select allOpen in new window

It works!! Thanks

Good one Guy!

Guy, I'm going to post another question about this passing Multi-Value Parameter.