Multi-value Parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Multi-value Parameter

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-12-11 : 06:33:21

Hi Guys

I am using the following stored procedure for a report that I have been creating:

CREATE PROCEDURE [dbo].[uspLogReturnedItemsFullRequiredList]
(
@StartDate DATETIME
,@EndDate DATETIME
,@ReasonCode INT
)
AS

SELECT
ri.receiptid AS 'Receipt Id'
,ri.receiptitemid AS 'Receipt Item Id'
,ri.sku AS 'Sku'
,ri.shortdescription AS 'Sku Description'
,vr.reasonname AS 'Reason'
,CONVERT(VARCHAR(8),ri.dateentered,112) AS 'Order Date'
,car.MethodName
,rd.City
,rd.County
,rd.Postcode
,i.CostPrice

FROM [dbo].[voiditem] vi (nolock)
INNER JOIN [dbo].[receiptitem] AS ri (nolock) ON vi.receiptitemid = ri.receiptitemid
INNER JOIN [dbo].[voidreason] AS vr (nolock) ON vi.enteredbyreasonid = vr.voidreasonid
LEFT JOIN [dbo].[Receipt] AS r WITH (NOLOCK) ON ri.receiptid = r.receiptid

LEFT JOIN (SELECT DISTINCT
ReceiptId
,CarrierMethodId
FROM dbo.DespatchConfirmation WITH (NOLOCK)) AS dc
ON r.ReceiptId = dc.ReceiptId

LEFT JOIN dbo.CarrierMethod AS car WITH (NOLOCK) ON dc.CarrierMethodId = car.CarrierMethodId
LEFT JOIN dbo.ReceiptDrop AS rd WITH (NOLOCK) ON r.ReceiptId = rd.ReceiptId
LEFT JOIN dbo.Inventory AS i WITH (NOLOCK) ON ri.InventoryId = i.InventoryId

WHERE ri.dateentered BETWEEN @StartDate AND DATEADD(d,+1,@EndDate)
AND vi.enteredbyreasonid IN (@ReasonCode )
ORDER BY enteredbyreasonid, ri.receiptid, CONVERT(VARCHAR(8),ri.dateentered,112)

For the @ReasonCode parameter I am using a Multi-value parameter type which I have created a dataset using the following query:
SELECT
VoidReasonId
,ReasonName
FROM dbo.VoidReason
WHERE VoidReasonId IN (1,2,3,4,5,6,7,8,9,10,11,12,14,15,21)

The VoidReasonId is the value field and ReasonName being the label field.

When I run the report I get the following error if I choose more than 1 value for the @ReasonCode ‘...Error converting data type nvarchar to int’

I understand that passing more than one value would show someting like 1,2,3 which is not an integer.

Does anyone know how I could solve this?

Thanking you in advance!!!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-11 : 07:46:00

Declare @ReasonCode as varchar and pivot the results of the query you are running to arrive at a string of codes separated with commas..

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-12-11 : 07:57:41

Hey

Thanks for that, but how would I pivot the results?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-11 : 09:40:23

http://www.sqlsnippets.com/en/topic-11787.html

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-12-11 : 10:22:54

I have a feeling that the values are being passed in the following way:

vi.enteredbyreasonid = '1,2,3'

instead of

vi.enteredbyreasonid IN N'1',N'2',N'3'

Do you know what could be happening?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 10:33:46

just try this


CREATE PROCEDURE [dbo].[uspLogReturnedItemsFullRequiredList]
(
@StartDate DATETIME
,@EndDate DATETIME
,@ReasonCode INT
)
AS

SELECT 
ri.receiptid AS 'Receipt Id'
,ri.receiptitemid AS 'Receipt Item Id'
,ri.sku AS 'Sku'
,ri.shortdescription AS 'Sku Description'
,vr.reasonname AS 'Reason'
,CONVERT(VARCHAR(8),ri.dateentered,112) AS 'Order Date'
,car.MethodName
,rd.City
,rd.County
,rd.Postcode
,i.CostPrice


FROM [dbo].[voiditem] vi (nolock) 
INNER JOIN [dbo].[receiptitem] AS ri (nolock) ON vi.receiptitemid = ri.receiptitemid
INNER JOIN [dbo].[voidreason] AS vr (nolock) ON vi.enteredbyreasonid = vr.voidreasonid
LEFT JOIN [dbo].[Receipt] AS r WITH (NOLOCK) ON ri.receiptid = r.receiptid

LEFT JOIN (SELECT DISTINCT 
ReceiptId
,CarrierMethodId
FROM dbo.DespatchConfirmation WITH (NOLOCK)) AS dc
ON r.ReceiptId = dc.ReceiptId

LEFT JOIN dbo.CarrierMethod AS car WITH (NOLOCK) ON dc.CarrierMethodId = car.CarrierMethodId
LEFT JOIN dbo.ReceiptDrop AS rd WITH (NOLOCK) ON r.ReceiptId = rd.ReceiptId
LEFT JOIN dbo.Inventory AS i WITH (NOLOCK) ON ri.InventoryId = i.InventoryId

WHERE ri.dateentered BETWEEN @StartDate AND DATEADD(d,+1,@EndDate)
AND  ','+ @ReasonCode + ',' LIKE '%,'+CAST(vi.enteredbyreasonid AS varchar(20)) + ',%'
ORDER BY enteredbyreasonid, ri.receiptid, CONVERT(VARCHAR(8),ri.dateentered,112)

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-12-11 : 10:42:23

Hey, thanks but still aint working, still tells me that it can't convert nvarchar to int!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 10:00:49

quote:
Originally posted by rcr69er

Hey, thanks but still aint working, still tells me that it can't convert nvarchar to int!

just noticed, why is @ReasonCode int if you're passing comma seperated list?it should be nvarchar


CREATE PROCEDURE [dbo].[uspLogReturnedItemsFullRequiredList]
(
@StartDate DATETIME
,@EndDate DATETIME
,@ReasonCode INT nvarchar(4000)
)
AS

SELECT 
ri.receiptid AS 'Receipt Id'
,ri.receiptitemid AS 'Receipt Item Id'
,ri.sku AS 'Sku'
,ri.shortdescription AS 'Sku Description'
,vr.reasonname AS 'Reason'
,CONVERT(VARCHAR(8),ri.dateentered,112) AS 'Order Date'
,car.MethodName
,rd.City
,rd.County
,rd.Postcode
,i.CostPrice


FROM [dbo].[voiditem] vi (nolock) 
INNER JOIN [dbo].[receiptitem] AS ri (nolock) ON vi.receiptitemid = ri.receiptitemid
INNER JOIN [dbo].[voidreason] AS vr (nolock) ON vi.enteredbyreasonid = vr.voidreasonid
LEFT JOIN [dbo].[Receipt] AS r WITH (NOLOCK) ON ri.receiptid = r.receiptid

LEFT JOIN (SELECT DISTINCT 
ReceiptId
,CarrierMethodId
FROM dbo.DespatchConfirmation WITH (NOLOCK)) AS dc
ON r.ReceiptId = dc.ReceiptId

LEFT JOIN dbo.CarrierMethod AS car WITH (NOLOCK) ON dc.CarrierMethodId = car.CarrierMethodId
LEFT JOIN dbo.ReceiptDrop AS rd WITH (NOLOCK) ON r.ReceiptId = rd.ReceiptId
LEFT JOIN dbo.Inventory AS i WITH (NOLOCK) ON ri.InventoryId = i.InventoryId

WHERE ri.dateentered BETWEEN @StartDate AND DATEADD(d,+1,@EndDate)
AND  ','+ @ReasonCode + ',' LIKE '%,'+CAST(vi.enteredbyreasonid AS nvarchar(20)) + ',%'
ORDER BY enteredbyreasonid, ri.receiptid, CONVERT(VARCHAR(8),ri.dateentered,112)

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-12-12 : 10:18:09

Hey!!

Thanks, Works GREAT now!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 10:26:32

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources