efficient select

It seems I should be able to do 1 select and both return that
recordset and be able to set a variable from that recordset.

eg.
Declare @.refid int
Select t.* from mytable as t --return the recordset
Set @.refid = t.refid

the above doesn't work-how can I do it without making a second trip to
the database?
Thanks,
Rick"Rick" <rick@.abasoftware.com> wrote in message
news:28d7cbb9.0409140717.9d794dc@.posting.google.co m...
> It seems I should be able to do 1 select and both return that
> recordset and be able to set a variable from that recordset.
> eg.
> Declare @.refid int
> Select t.* from mytable as t --return the recordset
> Set @.refid = t.refid
> the above doesn't work-how can I do it without making a second trip to
> the database?
> Thanks,
> Rick

select @.refid = t.refid from mytable where <your condition|||"strider5" <strider5@.s.zm.com> wrote in message
news:ci73p5$l4a$1@.domitilla.aioe.org...
> "Rick" <rick@.abasoftware.com> wrote in message
> news:28d7cbb9.0409140717.9d794dc@.posting.google.co m...
> > It seems I should be able to do 1 select and both return that
> > recordset and be able to set a variable from that recordset.
> > eg.
> > Declare @.refid int
> > Select t.* from mytable as t --return the recordset
> > Set @.refid = t.refid
> > the above doesn't work-how can I do it without making a second trip to
> > the database?
> > Thanks,
> > Rick
> select @.refid = t.refid from mytable where <your condition>

should be :
select @.refid = t.refid from mytable as t where <your condition|||Rick (rick@.abasoftware.com) writes:
> It seems I should be able to do 1 select and both return that
> recordset and be able to set a variable from that recordset.
> eg.
> Declare @.refid int
> Select t.* from mytable as t --return the recordset
> Set @.refid = t.refid
> the above doesn't work-how can I do it without making a second trip to
> the database?

You can't both return a result set and set a variable in the same
SELECT statement.

In many cases, it is not very ineffecient to access the table twice,
as the second read will be from cache. But if the search conditions
calls for a longer execution time, one possibility is to buffer the
result in a temp table or table variable, and then access that table
twice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp