Right haven't had time to check run this and its old code but the purpose is to show an elegant solution to what as originally coded was awful and bug ridden
There is an external table that is to be populated called #Cell_Matrix
The coder has NO idea of what the user range of selection or even different source tables but is required to populate this #Cell_Matrix table.
Its starts by invoking an stored procedure (a sub store procedure )
Then sets up counters because the will be a process of iteration depending on whatever?
Notice how the temp proc creates a temporary table that intefaces with a temp table created within the stored proc #CounterTlb and inserts results into the external table #Cell_Matrix
The iteration is simply and everything temporary is dropped and it avoids a nightmare of coding
CREATE PROCEDURE sp_report_Second_D
AS
EXEC sp_REPORT_second
DECLARE @xCounter integer
DECLARE @MaxId integer
CREATE TABLE #CounterTbl (
[ID] [int] IDENTITY(1,1) NOT NULL,
[GROUPCODE] [nvarchar](10) DEFAULT ('')
)
DECLARE @Counter INTEGER
SET @Counter = 1000
INSERT INTO #CounterTbl (GROUPCODE) VALUES('1')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('2')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('3')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('4')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('5')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('6')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('7')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('8')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('9')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('10')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('11')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('12')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('13')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('14')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('15')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('16')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('17')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('18')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('99')
INSERT INTO #CounterTbl (GROUPCODE) VALUES('100')
SET @xCounter = 1
SELECT @MaxId = count(id) FROM #CounterTbl
EXEC (' CREATE PROC #X ( @xCounter int, @Counter int )
AS
DECLARE @GROUPCODE INTEGER
DECLARE @D_GROUP VARCHAR(100)
DECLARE @h INTEGER
DECLARE @m INTEGER
DECLARE @O INTEGER
DECLARE @b-2 INTEGER
DECLARE @a INTEGER
DECLARE @C INTEGER
DECLARE @CR INTEGER
DECLARE @HA INTEGER
DECLARE @E INTEGER
DECLARE @CA INTEGER
DECLARE @SO INTEGER
DECLARE @ba INTEGER
DECLARE @MA INTEGER
DECLARE @an INTEGER
DECLARE @ALL INTEGER
DECLARE @OT INTEGER
DECLARE @PO INTEGER
DECLARE @PR INTEGER
DECLARE @MI INTEGER
DECLARE @NA INTEGER
DECLARE @Total INTEGER
DECLARE @Total integer
SELECT
@GROUPCODE=GR.[GROUPCODE],
@D_GROUP=[D_GROUP],
@h=[H],
@m=[M],
@O=[O],
@b-2=,
@a=[A (EXCLUDING E)],
@C=[C (EXCLUDING C)],
@CR=[CR],
@HA=[HA],
@E=[E],
@CA=[CA],
@SO=[SO],
@ba=[BA],
@MA=[MA],
@an=[AN],
@ALL=[ALL],
@OT=[OT],
@PO=[PO],
@PR=[PR],
@MI=[MI],
@NA=[N/A],
@Total=[Total]
from #aD
as GR JOIN #CounterTbl as dr ON dr.GROUPCODE=GR.GROUPCODE
WHERE DR.ID= @Counter
INSERT INTO #Cell_Matrix
SELECT @Counter, @h
INSERT INTO #Cell_Matrix
SELECT @Counter + 1 , @m
INSERT INTO #Cell_Matrix
SELECT @Counter + 2 , @O
INSERT INTO #Cell_Matrix
SELECT @Counter + 3 , @b-2
INSERT INTO #Cell_Matrix
SELECT @Counter + 4 , @a
INSERT INTO #Cell_Matrix
SELECT @Counter + 5 , @C
INSERT INTO #Cell_Matrix
SELECT @Counter + 6 , @CR
INSERT INTO #Cell_Matrix
SELECT @Counter + 7 , @h
INSERT INTO #Cell_Matrix
SELECT @Counter + 8 , @E
INSERT INTO #Cell_Matrix
SELECT @Counter + 9 , @CA
INSERT INTO #Cell_Matrix
SELECT @Counter + 10 , @SO
INSERT INTO #Cell_Matrix
SELECT @Counter + 11 , @ba
INSERT INTO #Cell_Matrix
SELECT @Counter + 12 , @MA
INSERT INTO #Cell_Matrix
SELECT @Counter + 13 , @an
INSERT INTO #Cell_Matrix
SELECT @Counter + 14 , @ALL
INSERT INTO #Cell_Matrix
SELECT @Counter + 15 , @OT
INSERT INTO #Cell_Matrix
SELECT @Counter + 16 , @PO
INSERT INTO #Cell_Matrix
SELECT @Counter + 17 , @PR
INSERT INTO #Cell_Matrix
SELECT @Counter + 18 , @MI
INSERT INTO #Cell_Matrix
SELECT @Counter + 19 , @NA
INSERT INTO #Cell_Matrix
SELECT @Counter + 20 , @Total
GO
')
Table_Loop:
IF @maxid >= @xCounter
BEGIN
EXEC #X @xCounter, @Counter
SET @Counter = @Counter + 21
SET @xCounter = @xCounter +1
GOTO Table_Loop
END
DROP PROC #X
GO