SQL Set Question

How can I make the following sql statement part of the "SET" in my query? I want to pull o开发者_开发技巧ut a list in a column and have my "SET" run against that. That way my "SET" isn't just one variable but rather a list to run against. Make sence?

DECLARE @Item as char(32)
declare @result varchar(max);
SET @Item = '034100-3JK'; 

I want to use something like the following to have it query against instead of the 034100-3JK:

select distinct
        a.t_sitm as StartItem
from 
        ttibom010101 as a 
        INNER JOIN ttiitm001101 as b on a.t_sitm = b.t_item 
where 
        b.t_ctyp != 'TOP'
        and b.t_ctyp != 'SUB'

---

EDITED: Based on asker's comments

I noticed that you are explicitly writing out the item code (or ID, whatever you want to call it). If you have a static list of codes that cannot be easily retrieved from your database, you can do something like this:

DECLARE @Items TABLE
(
  item varchar(10)
)
INSERT INTO @Items
VALUES ('034007-3CL'),
('next_code'),
('next_next_code')

Alternatively, if that list of codes can be grabbed easily:

DECLARE @Items TABLE
(
  item varchar(10)
)
INSERT INTO @Items
SELECT a.items
FROM mytable AS a
WHERE a.column = condition

Then you can use the variable like a normal table:

select 
   a.t_sitm as StartItem
   ,a.t_mitm
   ,a.t_sitm
   ,b.t_ctyp
   ,a.t_qana
from 
   ttibom010101 as a 
   INNER JOIN ttiitm001101 as b on a.t_sitm = b.t_item 
   INNER JOIN @items as c on a.t_sitm = c.item
where 
   b.t_ctyp != 'TOP'
   and b.t_ctyp != 'SUB'

---

DECLARE @Item as char(32) 
select distinct       @Item = a.t_sitm as StartItem 
from          ttibom010101 as a          
INNER JOIN ttiitm001101 as b 
   on a.t_sitm = b.t_item  
where  b.t_ctyp != 'TOP' and b.t_ctyp != 'SUB' 

Note this will only work correctly if the query wil never return more than one result. If you can havve multipel results, declare a table varaiable instead of a scalar varaible and insert into the table varaiable insted and then use it as a join in later processing.

---

You didn't mention which flavor of SQL you were using, but can you use a cursor or common table expression?

---

Question in conjunction with comment

Im trying to run this: select a.t_sitm as StartItem ,a.t_mitm ,a.t_sitm ,b.t_ctyp ,a.t_qana from ttibom010101 as a INNER JOIN ttiitm001101 as b on a.t_sitm = b.t_item where b.t_ctyp != 'TOP' and b.t_ctyp != 'SUB' and a.t_sitm =@Items

If all you are trying to do is test against a list, then use a constant list instead of a variable

and a.t_sitm in ('abc','def','ghi')

If you're trying to pass a single string parameter to SQL Server, then you need a function to split it into rows of a table, first grab the function dbo.Split from here (one of many usable "split" functions)

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput

Then you can use this query (IN form)

select a.t_sitm as StartItem ,a.t_mitm ,a.t_sitm ,b.t_ctyp ,a.t_qana
from ttibom010101 as a
INNER JOIN ttiitm001101 as b on a.t_sitm = b.t_item
where b.t_ctyp != 'TOP' and b.t_ctyp != 'SUB'
and a.t_sitm in (select Value from dbo.split(',',@item))

Or in JOIN form

select a.t_sitm as StartItem ,a.t_mitm ,a.t_sitm ,b.t_ctyp ,a.t_qana
from ttibom010101 as a
INNER JOIN ttiitm001101 as b on a.t_sitm = b.t_item
inner join dbo.split(',',@item) S on S.Value = a.t_sitm
where b.t_ctyp != 'TOP' and b.t_ctyp != 'SUB'