Parameter with multiple values

[annettes]

Hi Gurus,

I would like to know in what form the multiple value being passed into the script?
Problem : @Item is parameter allowed for multiple selection by user. Whenever I run the report, the report generated successfully with 1 value selected. When I select for multiple values, this error message prompted:
Query execution failed for dataset 'Dataset1'.
Incorrect syntax near ','.

The following is my code:

DECLARE @cslist VARCHAR(8000)
DECLARE @spot	INT
DECLARE	@str	VARCHAR(8000)
DECLARE	@sql	VARCHAR(8000)
DECLARE @parm	VARCHAR(8000)


set @cslist = replace((@Item),',')

WHILE @cslist <> ''
  BEGIN
	SET @spot = CHARINDEX(',', @cslist)
	IF @spot>0
	  BEGIN
		SET @str = convert(char, LEFT(@cslist , @spot-1))
		SET @cslist = RIGHT(@cslist , LEN(@cslist)-@spot)
	  END
	ELSE
	  BEGIN
		SET @str = convert(char,@cslist)
		SET @cslist = ''
	  END
set @parm=ltrim(@str)

****I perform some SQL selection based on @parm here****

END

Many thanks in advance

 

[RiverGuy]

If you write your query like this:

SELECT * FROM SomeTable WHERE SomeColumn IN (@SomeValues)

then it will rewrite the query for you on the back-end. No need for splitting a delimited list.

 

[annettes]

Dear RiverGuy,

Thanks for your reply.

I have to get the parameter displayed in the report as one of the column.

My table:
1) Item table
Item Description
----- -------------
111 Item1
222 Item2
333 Item3

2) Order table
Customer Item
-------- ------
AAA 111
AAA 222
BBB 111

The correct result retrieve should be:
Customer Item Description
-------- ----- -----------
AAA 333 Item3
BBB 222 Item2
BBB 333 Item3

My SQL selection:

select @parm as customer, item, description from 
Item where not exists 
 (select customer, item from Order where customer = (@parm))

I can't use "customer IN (@parameterFromScreen)", due to the result will total up by customer as follow: which is not correct:
Customer Item Description
-------- ---- -----------
NULL 333 Item3

Thanks.