Using local variable for "IN" criteria condition with SQL Server [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

SQL : in clause in storedprocedure:how to pass values

I'm using MS SQL Server 2005, and trying to basically script a 2-step process:

1. Query a table for a list of IDs matching certain criteria
2. Update a field in that table, where the ID is 开发者_JS百科in the list of IDs returned by the first

With the catch being that steps 1 and 2 might be separated by a considerable time delay and executed in different sessions. Essential the list of IDs used in #2 is historical data: the values which #1 returned at a past point in time.

What I've attempted to do is write all of IDs from #1 into a varchar(8000) in "##, ##, ##, ##," format (this part is working great), and then use that string like:

UPDATE table SET field=newValue WHERE (id IN (@varcharOfCommaSeparatedIDs))

But this is giving me a syntax error, stating that it cannot convert that varchar value into whatever is needed (the error message is being truncated)

Is there a way to do this without putting the entire SQL command into a string and executing that (using EXEC or sp_executesql)? After years of avoiding injection attacks I have a somewhat instinctive (and perhaps irrational) aversion to "dynamic SQL"

---

If you're passing the values around between SP's on the SQL Server, I highly recommend storing the values in tables...
- Temp Tables (#mytable)
- Table Variables (@table)
- Real Tables

In SQL Server 2008 onwards you can have table valued input parameters...

If you're passing the values in from an app, the dread comma-separated-string is indeed useful. There are many answers on SO that give Table Valued Functions for turning a string into a table of ids, read to be joined on.

SELECT
  *
FROM
  foo
INNER JOIN
  dbo.bar(@mystring) AS bar
    ON foo.id = bar.id

---

Just write it out to a table.

IF EXISTS (SELECT 1 FROM Database.dbo.MyHoldingTable)
DROP TABLE Database.dbo.MyHoldingTable

SELECT <fields>
INTO Database.dbo.MyHoldingTable
FROM <other table>
WHERE <conditions>

Then, later:

UPDATE OtherTable
Set Column=NewValue
WHERE ID IN (SELECT id FROM Database.dbo.MyHoldingTable)

Also note you could also use an INNER JOIN on your table instead of a IN clause if you prefer.