Put rows of data into a variable in SQL

[Bebe]

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

I keep getting this error when trying to return a select statement as an output variable.

for example:

SELECT @var = (SELECT item_id
FROM #Table
WHERE order_item IS NULL)

I need to have the rows that come back from this inside a variable -- how do i do this; can I?

[Mongo]

Use an aggregate function to return a single value. For example:

  SELECT @var = (SELECT COUNT(item_id)
                 FROM #Table 
                 WHERE order_item IS NULL)

[Bebe]

oh, I wan't to get all the rows coming back from that statement to represent a variable --The actual records not the count-- is it possible?

thanks

[Mongo]

Then just return your resultset.

  SELECT order_item
    , item_id 
    FROM #Table 
    WHERE order_item IS NULL

[Bebe]

yeah, i think i didn't think about my question. i was doing it that way. I was just absorbed in trying to make it into a variable.

thanks

[Mongo]

Sub SeeResult()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
  Set rs = New ADODB.Recordset
  Set cn = New ADODB.Connection
  With cn
    .ConnectionString = [your applicable connection string]
    .CursorLocation = adUseServer
    .Open
  End With
  '  Sometimes need this....
  ' cn.Execute "SET QUOTED_IDENTIFIER OFF"
  Set cmd = New ADODB.Command
  With cmd
    .CommandText = "sptest"         ' <-- your sp name here
    .CommandType = adCmdStoredProc
    .ActiveConnection = cn
  End With
  Set rs = cmd.Execute
  Do Until rs.EOF
    sz = ""
    For i = 0 To rs.Fields.Count - 1
      sz = sz & rs(i).Value & "  |  "
    Next i
    Debug.Print sz
    rs.MoveNext
  Loop
  ' and, of course, clean-up...
End Sub