SQL In range

[TRACEYMARYLAND]

hi i have
declare @str nvarchar(50),
@str1 nvarchar(50),
@str2 nvarchar(50),
@str3 nvarchar(50)

set @str1 = 'CHEMBIO'
set @str2 = 'DECON'
SELECT ORDERNUM, DESCR, VMFG.DBO.CUST_ORDER_LINE.PART_ID,LINE_NO,VMFG.DBO.PART.PRODUCT_CODE,* FROM PRODSCHED
INNER JOIN VMFG.DBO.CUSTOMER_ORDER ON PRODSCHED.ORDERNUM = VMFG.DBO.CUSTOMER_ORDER.ID
INNER JOIN VMFG.DBO.CUST_ORDER_LINE ON PRODSCHED.ORDERNUM = VMFG.DBO.CUST_ORDER_LINE.CUST_ORDER_ID
INNER JOIN VMFG.DBO.PART ON VMFG.DBO.CUST_ORDER_LINE.PART_ID = VMFG.DBO.PART.ID


WHERE VMFG.DBO.PART.PRODUCT_CODE IN (@str1, @str2)
ORDER BY ORDERNUM

Which works great.

Now i was wondering if im in ASP and allow a drop down list and allow user to select multiple im going to get

CHEMBIO,DECON

And i was wondering if i can do this
just have one variable
@str1 = 'CHEMBIO,DECON'

but when i try

WHERE VMFG.DBO.PART.PRODUCT_CODE IN (@str1)
i don't get anything as it trying to find CHEMBIO,DECON

Is there a way to do it.............

 

[Lothario]

Try this

REPLACE(@str1,",","','")

-L

 

[TRACEYMARYLAND]

Where would that code go before the select
?
Thanks

 

[Lothario]

@newstr1=REPLACE(@str1,",","','")

then use @newstr in your query...

-L

 

[TRACEYMARYLAND]

I just did this

CHARINDEX(',' + CAST(VMFG.DBO.PART.PRODUCT_CODE as varchar(100)) + ',',',' +
case @str1 when '' then CAST(VMFG.DBO.PART.PRODUCT_CODE as varchar(100)) else @str1 end + ',')>0


That seems to work