SQL Server Reporting and multi-values

[lauriemc]

I'm working with vb.net 2005, Business Applications, a SQL Server report. In my report I have parameters that are multi-valued - this option can be set in the Report Parameters box. When the client goes to run the report, the drop down list for the multi-value parameter is a series of check boxes. I think this is pretty handy -

 

except I can't get it to work in my stored procedure. If I pass in just one value, it works. If I pass in more than one value, it doesn't. The error message I get is 'Conversion failed when converting the nvarchar value '305,353' to data type int.

 

This is my stored procedure:

[highlight=sql]

CREATE PROCEDURE [dbo].[spTenderReport]

@Date_Begin datetime,

@Date_End datetime,

@STORENUM nvarchar(100),

@Tender nvarchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

 

-- Insert statements for procedure here

SELECT

StoreID,

[Name],

BatchNumber,

OpeningTime,

ClosingTime,

TransactionNumber,

Price,

Quantity,

[iD],

ItemLookupCode,

Description,

TenderID,

TenderDesc

 

FROM view_TenderReporting

 

WHERE OpeningTime >= @Date_Begin

AND ClosingTime <= @Date_End

 

AND StoreID IN (@STORENUM)

 

AND TenderID in (@Tender)

END

GO

[/highlight]

Right now I am only trying to send in the multi-values through @STORENUM, I haven't tried working with @Tender yet.

 

Could someone please help. I admit I am not well-versed in complex SQL, which I have a feeling is what my code needs.......:confused:

Edited August 5, 2008 by PlausiblyDamp