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
Summary Data of Repeatedly Executed SPROC
Posted - 10/31/2012 : 16:39:51
