Multi-Value Parameter

Question

Post reply

Multi-Value Parameter

rcr69er

Ten Centuries

Points: 1010
More actions
December 11, 2008 at 5:27 am

#197374

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!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

The two obvious choices are:

Resolve the values in @ReasonCode into a table or table variable, or convert the whole lot into dynamic sql.

There are some nifty methods of doing the former, and dynamic sql is horrible to read/debug/optimise.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rcr69er Ten Centuries Points: 1010 More actions · Answer 2

Hey

Thanks for that!

How would you do it the first way?

Sorry I'm quite new to SSRS!

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

rcr69er (12/11/2008)
Hey
Thanks for that!
How would you do it the first way?
Sorry I'm quite new to SSRS!
Thanks

No problem, here ya go...

[font="Courier New"]DECLARE @ReasonCode VARCHAR(100)

SET @ReasonCode = '1,2,4,8,16'

DROP TABLE #Reasons

CREATE TABLE #Reasons (Reason VARCHAR(20), ReasonCode INT)

INSERT INTO #Reasons (Reason, ReasonCode)

SELECT 'Reason 1', 1 UNION ALL

SELECT 'Reason 2', 2 UNION ALL

SELECT 'Reason 3', 3 UNION ALL

SELECT 'Reason 4', 4 UNION ALL

SELECT 'Reason 5', 5 UNION ALL

SELECT 'Reason 6', 6

SELECT r.*, c.*

FROM #Reasons r

INNER JOIN [dbo].[uftSplitString] (@ReasonCode, ',') c ON c.Item = r.ReasonCode

SELECT r.*

FROM #Reasons r

WHERE r.ReasonCode IN (SELECT Item FROM [dbo].[uftSplitString] (@ReasonCode, ',') )[/font]

Here's the function:

[font="Courier New"]CREATE FUNCTION [dbo].[uftSplitString]

(

@String VARCHAR(8000),

@Delimiter VARCHAR(255)

)

RETURNS

@Results TABLE

(

SeqNo INT IDENTITY(1, 1),

Item VARCHAR(8000)

)

AS

BEGIN

INSERT INTO @Results (Item)

SELECT SUBSTRING(@String+@Delimiter, number,

CHARINDEX(@Delimiter, @String+@Delimiter, number) - number)

FROM Numbers

WHERE number <= LEN(REPLACE(@String,' ','|'))

AND SUBSTRING(@Delimiter + @String,

number,

LEN(REPLACE(@Delimiter,' ','|'))) = @Delimiter

ORDER BY number RETURN

END

[/font]

I believe Jeff Moden is the original author of the function.

If you don't already have a numbers (tally) table, then you should read this:

http://www.sqlservercentral.com/articles/TSQL/62867/

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rcr69er Ten Centuries Points: 1010 More actions · Answer 4

rcr69er

Ten Centuries

Points: 1010

December 11, 2008 at 7:00 am

#911041

Hey, Thanks for that!!!