sql server - Use a variable in SQL Where clause that contains commas -

i have table in sql server 2008. table has field named cc_termid , numeric field. have data sitting on oracle server in find current termid's date in , use variable against sql table. pretty sure error in mismatch of data types, cannot figure out how store data variable.

here variable creating using openquery pull data want oracle:

    declare @terms nvarchar(50)     set @terms = (select * openquery(powerschool,     '     select to_number(listagg(id, '','') within group (order schoolid))     terms     sysdate + (select firstday - sysdate                      terms                      isyearrec = 1 , yearid = (select max(substr(termid, 1,2))                                                        cc) , schoolid = 51) between firstday , lastday , schoolid = 51    ')) 

this returns data 2700,2701,2703,2704

now want use variable in query against sql table this:

    select tch_email_addr, tch_last_name, tch_first_name, cc_termid     zps_ccraw     cc_termid in (@terms) 

of course error: "error converting data type nvarchar numeric". have tried cast/convert cc_termid nvarchar this:

    select tch_email_addr, tch_last_name, tch_first_name, cc_termid     zps_ccraw     cast(cc_termid nvarchar(50)) in (@terms) 

but no results returned. if remove @terms , type in 2700,2701,2703,2704 data returned expected.

i near expert when comes writing statements. know enough me trouble this, not enough know how out of it. can me achieve looking for?

you'll have forgive me, i'm not oracle, it's possible changes made subquery don't quite work. idea is, dont serialize list. leave set data.

  declare @terms table (id int)    insert @terms (id)   select id openquery   (       powerschool,       'select distinct id         terms        sysdate + (select firstday - sysdate                         terms                         isyearrec = 1 , yearid = (select max(substr(termid, 1,2))                                                           cc) , schoolid = 51) between firstday , lastday , schoolid = 51'   ) 

then change subsequent query this:

 select tch_email_addr, tch_last_name, tch_first_name, cc_termid  zps_ccraw  cc_termid in (select id @terms) 

or

 select tch_email_addr, tch_last_name, tch_first_name, cc_termid  zps_ccraw  inner join @terms b       on a.cc_termid = b.id 

note, same directly openquery rather dumping them in table variable if prefer.

what's happening have table looks like...

... cc_termid ... 2700 ... 2701 ... 2703 ... 2704  

...and you're saying "give me rows cc_termid equals string-literal "2700,2701,2703,2704"