title: Declare a variable list in SQL Server stored procedure
body: <p>I'd like to delete data from multiple tables with the same conditions (where clause) for each delete statement.</p>
  
  <pre><code>delete from tblA where id in (select x.id from tblX x where name like N'%test%')
  delete from tblB where id in (select x.id from tblX x where name like N'%test%')
  delete from tblC where id in (select x.id from tblX x where name like N'%test%')
  delete from tblD where id in (select x.id from tblX x where name like N'%test%')
  </code></pre>
  
  <p>Is there a way to declare a list that stores the ids from the select statement above?</p>
  
  <p>I tried:</p>
  
  <pre><code>declare @ids int
  set @ids = select x.id from tblX x where name like N'%test%'
  </code></pre>
  
  <p>But it complains that </p>
  
  <blockquote>
    <p><em>Subquery returned more than 1 value. This is not permitted when the
    subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as
    an expression.</em></p>
  </blockquote>
  
  <p>Please advise, thanks.</p>