SQL Server: setting variables from query output

Question

Hey all, I am wondering how to set variables to the output of the following query string:

SELECT count(*) fees_quantity,
   1.5 fees_price,
   1.5 * count(*) fees_amount,
   round(SUM((CONVERT(int,Points) * .1)),0)) redep_amount,
   round(SUM((CONVERT(int,Points) * .1)),0)) + 1.5 * count(*) total_amount
FROM tblHGP HGP,  
   OrderDetails OD, 
   tblInvoices i
  JOIN tblCS cs ON i.INumber = cs.INumber
  JOIN tblECI ac ON i.INumber = ac.INumber 
WHERE cs.SoldTo = HGP.ECard 
AND issued BETWEEN '2010-09-01' AND '2010-09-30 23:59:59' 
AND Country = 'US' 
AND HGP.iNumber = OD.orderdetail

How can I do something like this?

DECLARE @FeesQty varchar(3)
DECLARE @FeesTotal varchar(10)
DECLARE @RedepTotal varchar(10)
DECLARE @Total varchar(10)

@FeesQty = fees_quantity
@FeesTotal = fees_amount
@RedepTotal = redep_amount
@Total = total_amount

How can that be done?

Thanks!

David

Answer 1

Like this:

DECLARE @FeesQty varchar(3)
DECLARE @FeesTotal varchar(10)
DECLARE @RedepTotal varchar(10)
DECLARE @Total varchar(10)

SELECT
    @FeesQty = count(*),
    @FeesTotal = 1.5 * count(*),
    @RedepTotal = round(SUM((CONVERT(int,Points) * .1)),0)),
    @Total = round(SUM((CONVERT(int,Points) * .1)),0)) + 1.5 * count(*)
FROM tblHGP HGP,  
   OrderDetails OD, 
   tblInvoices i
  JOIN tblCS cs ON i.INumber = cs.INumber
  JOIN tblECI ac ON i.INumber = ac.INumber 
WHERE cs.SoldTo = HGP.ECard 
AND issued BETWEEN '2010-09-01' AND '2010-09-30 23:59:59' 
AND Country = 'US' 
AND HGP.iNumber = OD.orderdetail

Or - I suppose - If you didn't want to modify your query:

DECLARE @FeesQty varchar(3)
DECLARE @FeesTotal varchar(10)
DECLARE @RedepTotal varchar(10)
DECLARE @Total varchar(10)

SELECT
    @FeesQty = t.fees_quantity,
    @FeesTotal = t.fees_amount,
    @RedepTotal = t.redep_amount,
    @Total = t.total_amount
FROM (
    SELECT count(*) fees_quantity,
       1.5 fees_price,
       1.5 * count(*) fees_amount,
       round(SUM((CONVERT(int,Points) * .1)),0)) redep_amount,
       round(SUM((CONVERT(int,Points) * .1)),0)) + 1.5 * count(*) total_amount
    FROM tblHGP HGP,  
       OrderDetails OD, 
       tblInvoices i
      JOIN tblCS cs ON i.INumber = cs.INumber
      JOIN tblECI ac ON i.INumber = ac.INumber 
    WHERE cs.SoldTo = HGP.ECard 
    AND issued BETWEEN '2010-09-01' AND '2010-09-30 23:59:59' 
    AND Country = 'US' 
    AND HGP.iNumber = OD.orderdetail
) t