title: Using temporary table in where clause
body: <p>I want to delete many rows with the same set of field values in some (6) tables. I could do this by deleting the result of one subquery in every table (Solution 1), which would be redundant, because the subquery would be the same every time; so I want to store the result of the subquery in a temporary table and delete the value of each row (of the temp table) in the tables (Solution 2). Which solution is the better one?</p>
  
  <p>First solution:</p>
  
  <pre><code>DELETE FROM dbo.SubProtocols
  WHERE ProtocolID IN (
      SELECT ProtocolID
      FROM dbo.Protocols
      WHERE WorkplaceID = @WorkplaceID
  )
  
  DELETE FROM dbo.ProtocolHeaders
  WHERE ProtocolID IN (
      SELECT ProtocolID
      FROM dbo.Protocols
      WHERE WorkplaceID = @WorkplaceID
  )
  
  // ...
  
  DELETE FROM dbo.Protocols
  WHERE WorkplaceID = @WorkplaceID
  </code></pre>
  
  <p>Second Solution:</p>
  
  <pre><code>DECLARE @Protocols table(ProtocolID int NOT NULL)
  
  INSERT INTO @Protocols
  SELECT ProtocolID
  FROM dbo.Protocols
  WHERE WorkplaceID = @WorkplaceID
  
  DELETE FROM dbo.SubProtocols
  WHERE ProtocolID IN (
      SELECT ProtocolID
      FROM @Protocols
  )
  
  DELETE FROM dbo.ProtocolHeaders
  WHERE ProtocolID IN (
      SELECT ProtocolID
      FROM @Protocols
  )
  
  // ...
  
  DELETE FROM dbo.Protocols
  WHERE WorkplaceID = @WorkplaceID
  </code></pre>
  
  <p>Is it possible to do solution 2 without the subquery? Say doing <code>WHERE ProtocolID IN @Protocols</code> (but syntactically correct)?</p>
  
  <p>I am using Microsoft SQL Server 2005.</p>