Use a variable in SQL Where clause that contains commas

Question

I have a table that is in SQL server 2008. The table has a field named CC_TERMID and it is a numeric field. I have data sitting on an oracle server in which I would like to find the current TermID's for the date we are in and I would like to use this as a variable against my SQL table. I am pretty sure my error is in the mismatch of data types, but I cannot figure out how to store the data for the variable.

Here is my variable I am creating using openquery to pull the data I want from Oracle:

    DECLARE @TERMS nvarchar(50)
    SET @TERMS = (SELECT * FROM OPENQUERY(POWERSCHOOL,
    '
    SELECT TO_NUMBER(LISTAGG(ID, '','') WITHIN GROUP (ORDER BY SCHOOLID))
    FROM TERMS
    WHERE SYSDATE + (SELECT FIRSTDAY - SYSDATE
                     FROM TERMS
                     WHERE ISYEARREC = 1 AND YEARID = (SELECT MAX(SUBSTR(TERMID, 1,2))
                                                       FROM CC) AND SCHOOLID = 51) BETWEEN FIRSTDAY AND LASTDAY AND SCHOOLID = 51
   '))

This returns data like 2700,2701,2703,2704

Now I want to use that variable in a query against my SQL table like this:

    SELECT TCH_EMAIL_ADDR, TCH_LAST_NAME, TCH_FIRST_NAME, CC_TERMID
    FROM ZPS_CCRAW
    WHERE CC_TERMID IN (@TERMS)

Of course I get the error: "Error converting data type nvarchar to numeric". I have tried to CAST/CONVERT CC_TERMID to a nvarchar like this:

    SELECT TCH_EMAIL_ADDR, TCH_LAST_NAME, TCH_FIRST_NAME, CC_TERMID
    FROM ZPS_CCRAW
    WHERE CAST(CC_TERMID AS NVARCHAR(50)) IN (@TERMS)

But I get no results returned. If I remove the @TERMS and I type in 2700,2701,2703,2704 then I will get data returned as expected.

I am nowhere near an expert when it comes to writing statements. I know enough to get me into trouble like this, but not enough to know how to get out of it. Can anyone help me achieve what I am looking for?

Answer 1

You'll have to forgive me, I'm not good with Oracle, so it's possible the changes I made to your subquery don't quite work. But Basically the idea is, dont serialize the list. Just leave it as set data.

  DECLARE @TERMS table (Id int)

  insert into @TERMS (Id)
  SELECT ID FROM OPENQUERY
  (
      POWERSCHOOL,
      'SELECT DISTINCT ID 
       FROM TERMS
       WHERE SYSDATE + (SELECT FIRSTDAY - SYSDATE
                        FROM TERMS
                        WHERE ISYEARREC = 1 AND YEARID = (SELECT MAX(SUBSTR(TERMID, 1,2))
                                                          FROM CC) AND SCHOOLID = 51) BETWEEN FIRSTDAY AND LASTDAY AND SCHOOLID = 51'
  )

Then change your subsequent query to something like this:

 SELECT TCH_EMAIL_ADDR, TCH_LAST_NAME, TCH_FIRST_NAME, CC_TERMID
 FROM ZPS_CCRAW
 WHERE CC_TERMID IN (select Id @TERMS)

or

 SELECT TCH_EMAIL_ADDR, TCH_LAST_NAME, TCH_FIRST_NAME, CC_TERMID
 FROM ZPS_CCRAW a
 INNER JOIN @TERMS b
      on a.CC_TERMID = b.Id

Note, you could do the same directly from OPENQUERY rather than dumping them in a table variable if you prefer.

What's basically happening is you have a table that looks like...

... CC_TERMID
... 2700
... 2701
... 2703
... 2704 

...and you're currently saying "Give me all the rows where CC_TERMID equals the string-literal "2700,2701,2703,2704"