SSRS Pass Multi-Value Parameter to Stored Procedure

I created this stored procedure for a SSRS report, but I need to pass multi-value parameters to @StoreID and @ItemStatus. The parameters in the SSRS report are named the same thing as in the stored procedure. I need some help with the additional steps to make this happen. Multi-value from SSRS does not pass through easily.

CREATE PROCEDURE sp_RedemptionAdjustmentAudit

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

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 (@StoreID)) 
			 AND (rdd.enable IN (@ItemStatus)) 
			 
ORDER BY		rdd.StoreID, rdd.itmCode, rdd.TransactionDate

GO

Open in new window

dk04Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
sorry, but you still have this line in the first SELECT:
WHERE        (StoreID IN (@StoreID))

and it should be:
WHERE        (StoreID IN ( SELECT  CAST(value as int) from dbo.ParmsToList(@StoreID, ',')  ) )
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please see my article on how to solve that part
WHERE yourfield IN (@variable)

http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
0
 
David ToddSenior DBACommented:
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)
				)
	;

Open in new window

     

HTH
  David
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
dk04Author Commented:
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".
0
 
ValentinoVBI ConsultantCommented:
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.
0
 
dk04Author Commented:
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

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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, ',')  )
0
 
dk04Author Commented:
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,',') )

Open in new window

0
 
ValentinoVBI ConsultantCommented:
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)...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, good catch :)
0
 
ValentinoVBI ConsultantCommented:
A3: ooh, thank you Sir! :)
0
 
dk04Author Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please show the current code of the stored proc?
0
 
ValentinoVBI ConsultantCommented:
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)...
0
 
dk04Author Commented:
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

Open in new window

0
 
dk04Author Commented:
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

Open in new window

0
 
dk04Author Commented:
It works!! Thanks
0
 
ValentinoVBI ConsultantCommented:
Good one Guy!
0
 
dk04Author Commented:
Guy, I'm going to post another question about this passing Multi-Value Parameter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.