COMPUTE results to variables?

Question

Post reply

COMPUTE results to variables?

tembotxus

SSC-Addicted

Points: 491
More actions
November 30, 2007 at 10:06 am

#179734

Hello
Does anyone know if it is possible to somehow assign the COMPUTE results into local variables inside the stored procedure? The developer would like them returned as ouput parameter values (eg. @SumTotal, @AvgTotal), instead of having to deal with the second result set returned by the stored proc. I'm guessing the answer is no, but thought I'd check with the group. (Using AdventureWorks as example db)
DECLARE @sum money, @avg money
EXECUTE TestProc @sum OUT, @avg OUTPUT
CREATE PROCEDURE TestProc
@SumTotal money OUTPUT,
@AvgTotal money OUTPUT
AS
SET NOCOUNT ON
SELECT SalesOrderID, LineTotal
FROM Sales.SalesOrderDetail
COMPUTE SUM(Linetotal), AVG(Linetotal)
---
Thanks,
Joe

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

No,

a second query is in order

alter PROCEDURE TestProc

@SumTotal money OUTPUT,

@AvgTotal money OUTPUT

AS

SET NOCOUNT ON

SELECT @SumTotal = Sum(LineTotal), @AvgTotal = Avg(LineTotal)

FROM Sales.SalesOrderDetail

SELECT SalesOrderID, LineTotal

FROM Sales.SalesOrderDetail

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Use WITH ROLLUP... search for the correct "NULLS"...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 3

I Second the ROLLUP sugesstion

In addition COMPUTE and COMPUTE BY clauses are provided for backward compatibility only.

Cheers,

* Noel