Showing posts with label onlyalter. Show all posts
Showing posts with label onlyalter. Show all posts

Friday, March 30, 2012

Return COMPUTE from stored proc

I'm trying to figure out how to get my stored proc below to just return the result for COMPUTE only:
ALTER PROCEDURE [dbo].[procname]
AS
BEGIN
Select (cast(FGoal as numeric(30,2)) / FSched) * 100 AS gt
from DR WHERE e='06'
group by CustomerName,
CustomerNumber,
FGoal,
FSched
order by CustomerNumber
COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100)
END
When my stored proc is run, it should only return one value, the result of COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100). Right now however, it returns only the list from the select (below) which I don't want, I just want the COMPUTE value returned which is one value (the sum of the items below):
27256.000000
14218.000000
0.000000
14930.000000
54824.000000
148616.666667
73320.000000
85956.000000
105507.500000
67911.904762
55276.190476
14467.500000
5985.000000
20910.000000
118784.000000
5340.000000
5295.000000
567.500000

It is not possible to modify behavior of COMPUTE. It is a non-standard / proprietary extension so you should avoid using it. Instead write your own query using say ROLLUP/CUBE operator to get the sum at the desired levels. This will allow you to control the output rows by filtering on GROUPING function return values. See BOL for examples.|||

Thanks figured that after spending half my day trying. I found a way to sum what I needed and return one GT without compute:

Select Total = SUM(gt)

FROM

(Select DistinctCustomerName,

CustomerNumber,

FeeGoal_AZ AS FG,

FeeSchedule,

(cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt

from DCR WHERE branch='00002'

group by CustomerName,

CustomerNumber,

FeeGoal_AZ,

FeeSchedule

) as dTable