Solved

SSRS Pass Multi-Value Parameter to Stored Procedure

Posted on 2014-02-19
19
5,403 Views
Last Modified: 2014-11-28
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

0
Comment
Question by:dk04
  • 8
  • 5
  • 5
  • +1
19 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
 

Author Comment

by:dk04
Comment Utility
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
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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
 

Author Comment

by:dk04
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:dk04
Comment Utility
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
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
yes, good catch :)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
A3: ooh, thank you Sir! :)
0
 

Author Comment

by:dk04
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
can you please show the current code of the stored proc?
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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
 

Author Comment

by:dk04
Comment Utility
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
 

Author Comment

by:dk04
Comment Utility
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 

Author Comment

by:dk04
Comment Utility
It works!! Thanks
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
Good one Guy!
0
 

Author Comment

by:dk04
Comment Utility
Guy, I'm going to post another question about this passing Multi-Value Parameter.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now