3

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 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"

Community
  • 1
  • 1
DaveD
  • 2,196
  • 1
  • 23
  • 33
  • 1
    This is doable, but the solutions are either ugly hacks or way too complicated. I found a few explanations on SO last week when I searched for this. – Teddy Jul 14 '11 at 14:34
  • The syntax you have used isn't valid, however there are techniques that can be used when passing CSV data to stored procedures. Be aware that most of them a fairly ugly, but they do get the job done. – Justin Jul 14 '11 at 14:34
  • @Kragen - it's not a dupe since his requirement is to have the list passed between different procs/sessions. – JNK Jul 14 '11 at 14:43
  • Kragen's right, that question is close enough to answer mine. It's not exactly the same scenario, but I need to have the results of the first step in a format which can be stored outside of SQL (such as in a string or resultset in an application) and then passed back to #2 (which limits me to a string/varchar) later on. I probably should have explicitly stated that requirement in the question, my apologies for not doing so, and thanks to everyone who replied... Now how can I mark this as answered/dupe? – DaveD Jul 14 '11 at 14:54

2 Answers2

1

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
MatBailie
  • 83,401
  • 18
  • 103
  • 137
0

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.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • I can't clutter up this production environment with tables used for scripts like this :( – DaveD Jul 14 '11 at 14:39
  • @Dave - It's not clutter. Put it into a `WorkTable` database, drop it when you are done with it. If your requirement is "Make a list and then some unspecified time later in another sessions or proc reference that list" this is how you do it. – JNK Jul 14 '11 at 14:41
  • Or use Temporary Tables or Table Variables? But try not to store record sets as strings if it can be avoided. (imho) – MatBailie Jul 14 '11 at 14:45
  • Our definitions of clutter are different ;) For the specific project I'm working on, the list of values returned from step #1 simply cannot be stored in the DB, the script needs to be non-intrusive and do any long-term storage outside of SQL. – DaveD Jul 14 '11 at 14:45
  • @Dave - then store the list in your application layer. If you use the same session you can store the date in a `#temp` table if needed. – JNK Jul 14 '11 at 14:47