TSQL - efficient way of setting multiple variables

Question

is their a more efficient way of doing this?

set @ShippingL = (select ShippingL from AuctionProducts where ProductID = @ProductID)
set @ShippingB = (select ShippingB from AuctionProducts where ProductID = @ProductID)
set @ShippingH = (select ShippingH from AuctionProducts where ProductID = @ProductID)
set @ShippingW = (select ShippingW from AuctionProducts where ProductID = @ProductID)

Cheers, -R

Answer 1

I would think doing one query would be as good as you'll get it:

select 
  @ShippingL = ShippingL,
  @ShippingB = ShippingB,
  @ShippingH = ShippingH,
  @ShippingW = ShippingW 
from 
  AuctionProducts 
where
  ProductID = @ProductID 

I would imagine this is 4 times faster than the code that you posted. Also, make sure that you have an index defined on the ProductID column of the AuctionProducts table.