Select multiple values within a single Declare statement

Question

DECLARE @account VARCHAR(12)  
SET @account = '49943725'  

DECLARE @items VARCHAR(5)  
SET @items = (SELECT item_no FROM transactions   
               WHERE account = @account   
               AND item_no IN ('81','101','108','112','113','118','187','189','190','192','193','194','195'))

SELECT 
    property, CONVERT(VARCHAR(10), account) AS account,
    CONVERT(VARCHAR(5), item_no) AS item_no,
    CONVERT(VARCHAR(9), amount) AS amount,  
    CONVERT(VARCHAR(9), amt_paid) AS amt_paid, 
    status,
    CONVERT(VARCHAR(8), tran_id) AS tran_id, 
    CONVERT(VARCHAR(11), post_date) AS post_date, 
    tran_code, 
    CONVERT(VARCHAR(25), notes) AS notes, 
    clk, invoice, charge_property, * 
FROM 
    transactions 
WHERE 
    account = @account AND item_no = @items

Error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer 1

Any tips on how to make @items = multiple values?

No - a VARCHAR(5) variable can only hold one value. You can, howvere, just embed your list as a subquery:

SELECT 
     property, 
     CONVERT(VARCHAR(10), account) AS account,
     CONVERT(VARCHAR(5), item_no) AS item_no,
     CONVERT(VARCHAR(9), amount) AS amount,  
     CONVERT(VARCHAR(9), amt_paid) AS amt_paid, status,
     CONVERT(VARCHAR(8), tran_id) AS tran_id, 
     CONVERT(VARCHAR(11), post_date) AS post_date, tran_code, 
     CONVERT(VARCHAR(25), notes) AS notes, clk, invoice, charge_property, *
FROM transactions 
WHERE account = @account 
AND item_no IN (SELECT item_no FROM transactions   
               WHERE account = @account   
               AND item_no IN        
                ('81','101','108','112','113','118','187','189','190','192','193','194','195'))