Hi Simon

Thanks heaps for the reply, it works perfectly, i had to change the syntax
slightly because the procedure was using @MachineName as the data to search
for instead of the contents of the variable, so it looked like this

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

Thanks again


"Simon Hayes" <sql@hayes.ch> wrote in message
news:3ffbfea2$1_1@news.bluewin.ch...
>
> "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
>
>