"Jarrod Morrison" <jarrodm@ihug.com.au> wrote in message
news:btgib8$9gq$1@lust.ihug.co.nz...
> Hi All
>
> Im unsure of how to use vb to read the results of my stored procedure. Ive
> included the stored procedure at the end of this message for reference.
> Basically the stored procedure will first of all construct a select string
> based on the your computer name and perform this select on a table named
> Locations, next it will perform a similar search on a table called
> CustLocations. Normally it will return two recordsets in query analyzer
that
> for example would look like:
>

<snip>

I don't have enough VB knowledge to respond to the issue of multiple
recordsets, but one possible approach would be to return a single result set
from your procedure:

EXEC (@SqlStr + ' UNION ALL SELECT LocationID FROM CustLocations WHERE
MachineName = @MachineName')

You may want to use UNION instead of UNION ALL, if you want to remove
duplicate values (see Books Online). Note that this approach means you have
no way to tell the difference between LocationID values from Locations and
those from CustLocations. If this is important, then you can add a dummy
flag column to your query to indicate the source:

/* L for Location */

SET @SqlStr = 'SELECT ''L'' AS ''Source'', LocationID FROM Locations WHERE
GroupID = '''

/* C for CustLocation */

EXEC (@SqlStr + ' UNION ALL SELECT ''C'', LocationID FROM CustLocations
WHERE MachineName = @MachineName ORDER BY ''Source'' DESC')


Simon