Solved

SSRS Pass Multi-Value Parameter to Stored Procedure

Posted on 2014-02-19
19
5,438 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
ID: 39870618
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
ID: 39872000
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
ID: 39872263
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
ID: 39872581
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
ID: 39873584
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]
ID: 39873604
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
ID: 39873700
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
ID: 39873996
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]
ID: 39874002
yes, good catch :)
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 39876073
A3: ooh, thank you Sir! :)
0
 

Author Comment

by:dk04
ID: 39899268
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]
ID: 39899802
can you please show the current code of the stored proc?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39902709
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
ID: 39909515
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
ID: 39909548
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
ID: 39909641
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
ID: 39909856
It works!! Thanks
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39910301
Good one Guy!
0
 

Author Comment

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 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

23 Experts available now in Live!

Get 1:1 Help Now