ALTER PROCEDURE [dbo].[specso_WarrantsByZone_Report]
@yearval VARCHAR(500),
AS 
SELECT 
FROM  (SELECT CAST('<Node><Element>' + REPLACE(@yearval,',','</Element><Element>') + '</Element></Node>' AS xml) AS XMLval) t
INNER JOIN yourtable t1
ON XMLval.exist('/Node/Element[.=sql:column("yearfield")]')=1

SELECT NAME
FROM MBI..MBI WITH (NOLOCK)
INNER JOIN MBI..MBI_ADD WITH (NOLOCK)
ON MBI_ADD.ECSOID = MBI.ECSOID
INNER JOIN ART..ART WITH (NOLOCK)
ON ART.ECSOID = MBI.ECSOID
INNER JOIN ART..ARR_AFSS WITH (NOLOCK)
ON ART.ARTNO = ARR_AFSS.ARTNO
INNER JOIN (SELECT CAST('<Node><Element>' + REPLACE(@yearval,',','</Element><Element>') + '</Element></Node>' AS xml) AS XMLval) t
ON XMLval.exist('/Node/Element[.=sql:column("Youryearfieldhere")]')=1
WHERE ART.ARTTYPE = 'W' 
AND ART.WARSTATUS = 'W' 

SELECT NAME
FROM MBI..MBI WITH (NOLOCK)
inner join MBI..MBI_ADD WITH (NOLOCK)
       on MBI_ADD.ECSOID = MBI.ECSOID
inner join ART..ART WITH (NOLOCK)
       on ART.ECSOID = MBI.ECSOID 
inner join ART..ARR_AFSS WITH (NOLOCK)
       on ARR_AFSS.ARTNO = ART.ARTNO 

//ADD CONDITION FOR YEAR VALUE HERE
inner join dbo.fn_splitFunction(@yearval, ',') f
       on yearfield like f.<functionReturnColumn> + '%'

WHERE  ART.ARTTYPE = 'W' 
AND    ART.WARSTATUS = 'W' 

quote:

---

Originally posted by fralo

Thank so much Vis. Does your select cast handle LIKE? My incoming values will be like ECSO12ARR, ECSO13ARR but I need to search for anything that begins with that: 'ECSO12ARR%' OR 'ECSO13ARR%'.

Also, "Youryearfieldhere" refers to the database filed, correct?

---

declare @t table
(
id int,
field varchar(20)
)

insert @t
values(1,'test'),
(2,'giu'),
(3,'pieceofmeal'),
(4,'csvtext'),
(5,'master'),
(7,'pattern')

declare @t1 table
(
id int,
list varchar(1000)
)

insert @t1
values (1,'this,is,test,for,csv,search,piece'),
(2,'finding,string,pattern,within,given,csv,value')

SELECT t.id,field,list
FROM @t t
INNER JOIN (SELECT id,list,CAST('<Node><Row>' + replace(list,',','</Row><Row>') + '</Row></Node>' as xml) AS XMLList FROm @t1) t1
ON XMLList.exist('/Node/Row/text()[contains(sql:column("field"),.)]')=1


output
--------------------------------------------------------------
id	field	        list
--------------------------------------------------------------
1	test	        this,is,test,for,csv,search,piece
3	pieceofmeal	this,is,test,for,csv,search,piece
4	csvtext	        this,is,test,for,csv,search,piece
4	csvtext	        finding,string,pattern,within,given,csv,value
7	pattern	        finding,string,pattern,within,given,csv,value