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.......
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.......
Last edited by a moderator: