SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Summary Data of Repeatedly Executed SPROC
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

clevesteve
Starting Member

3 Posts

Posted - 10/31/2012 :  16:39:51  Show Profile  Reply with Quote
I have a stored procedure which performs many calculations and a few data pulls for a specific input. The output (the select statement) gives the calculation result, the input value, and an attribute value.

I want to create a summary table for this calculation performed on many inputs grouped by the attribute... basically my output will need to be:

Average Value		Attribute	Quantity
AVG(CalculationValue)	ATTVALUE1	COUNT(CalculationValue)
AVG(CalculationValue)	ATTVALUE2	COUNT(CalculationValue)


Again, CalculationValue and Attribute come from a stored procedure. Does anyone have any suggestions for this? Thanks!

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 10/31/2012 :  17:00:41  Show Profile  Reply with Quote
I'm not clear on what you are trying to do. Are you trying to aggregate the result of a stored procedure call or is it something else?

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

clevesteve
Starting Member

3 Posts

Posted - 10/31/2012 :  17:15:36  Show Profile  Reply with Quote
I have to call the single-input stored procedure many times based on a list of inputs, then I want to aggregate/summarize the results of all the executions.

Each execution of the stored procedure yields a real (the calculation value -- I want to get the average of this), an nvarchar(10) (equivalent to the input value of the stored procedure... an identitiy value), and an nvarchar(4) (the attribute value I want to group my summary by.)

Edited by - clevesteve on 10/31/2012 17:21:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/01/2012 :  09:59:24  Show Profile  Reply with Quote
quote:
Originally posted by clevesteve

I have to call the single-input stored procedure many times based on a list of inputs, then I want to aggregate/summarize the results of all the executions.

Each execution of the stored procedure yields a real (the calculation value -- I want to get the average of this), an nvarchar(10) (equivalent to the input value of the stored procedure... an identitiy value), and an nvarchar(4) (the attribute value I want to group my summary by.)


why do you need to do this row by row?
Why cant it be set based? Can you explain your full scenario? are you using these return value from procedure somewhere?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

clevesteve
Starting Member

3 Posts

Posted - 11/01/2012 :  10:09:36  Show Profile  Reply with Quote
For anyone who is interested, I've solved my problem with some hunting around... getting examples for different things here and there.

First thing I do is create a temp table with columns to accept the outputs of the SPROC.
Second thing is to create a cursor for the list of identities I want to run my SPROC on, then open the cursor.
Third step is to FETCH NEXT from the cursor into a variable to pass to the SPROC.
Then, start a WHILE @@Fetch_Status = 0 insert into the temp table the output of the SPROC and increment the cursor.
Get summary data from temp table.

Here's my syntax:
DECLARE @myidentity nvarchar(10)
CREATE TABLE #MyTempTable (
MYCALCULATIONVALUE real,
IDCOLUMN nvarchar(10),
MYATTRIBUTE nvarchar(4))

DECLARE cursorname CURSOR FOR SELECT idcolumn
FROM MyDatatable
WHERE <my where clauses>

OPEN cursorname

FETCH NEXT FROM cursorname INTO @myidentity

WHILE @@Fetch_Status = 0 BEGIN
INSERT INTO #MyTempTable EXEC myschema.mysproc @myidentity
FETCH NEXT FROM cursorname INTO @myidentity
END

CLOSE cursorname
DEALLOCATE cursorname

SELECT 
    ROUND(AVG(p.MYCALCULATIONVALUE),2) 'MyResult', 
    p.MYATTRIBUTE, 
    COUNT(p.MYCALCULATIONVALUE) 'Quantity', 
    DATEPART(ww,o.DATECOLUMN)
FROM #MyTempTable p, OtherTable o
WHERE p.IDCOLUMN = o.IDCOLUMN
GROUP BY p.MYATTRIBUTE, DATEPART(ww,o.DATECOLUMN)

DROP TABLE #MyTempTable
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000