Cursor and sub loop | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Cursor and sub loop

Hi… Trying to do something very simple, so I thought, but doesn’t seem to work.
I want to list a load of IDs from a table and also list the month. But when I include a loop inside the cursor loop I only get the first row. Any advice or anything I am doing wrong?
DECLARE PROJECT_CURSOR CURSOR FOR SELECT DISTINCT PROJECTID FROM WM_PROJECTS_BIDS
OPEN PROJECT_CURSOR DECLARE @THEPROJID DECIMAL(9)
DECLARE @THEMONTH INT SET @THEMONTH = 0 FETCH NEXT FROM PROJECT_CURSOR INTO @THEPROJID
WHILE @@FETCH_STATUS <> -1
BEGIN WHILE @THEMONTH < 13
BEGIN SET @THEMONTH = @THEMONTH + 1
PRINT CAST(@THEPROJID AS VARCHAR) + ‘ ‘ + CAST(@THEMONTH AS VARCHAR) END FETCH NEXT FROM PROJECT_CURSOR INTO @THEPROJID
END CLOSE PROJECT_CURSOR
DEALLOCATE PROJECT_CURSOR
Can you please post some sample data and what your result should look like? I don’t think you need a cursor at all. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
OK turned out I didn’t reset the @THEMONTH = 0 inside the cursor loop so after the first pass, @THEMONTH always = 13 hence it appeared I only got 1 row. Here is my dilemma: Table "Budget" – (BudgetID decimal(9),Year INT,Month INT,Code decimal(9,3),Value float)
Table "SystemBudget" – (SysBudgetID decimal(9),Code decimal(9,3),SpecialCode varchar(10)) Data: Table Budget
|_____BudgetID_____|_____YEAR_____|_____MONTH_____|_____CODE_____|__VALUE__| _________5_________|_____20006____|_______3_______|_____4.001____|_1023.45_| Data: Table SystemBudget
|____SysBudgetID____|_____CODE_____|_____SPECIALCODE_____| _________1__________|_____4.001____|_______3_____________|
_________2__________|_____4.002____|_______3_____________| What I need returned: 2006 | 1 | 4.001 | 0.00
2006 | 2 | 4.001 | 0.00
2006 | 3 | 4.001 | 1023.45
2006 | 4 | 4.001 | 0.00
2006 | 5 | 4.001 | 0.00
2006 | 6 | 4.001 | 0.00
etc up to 12 for each code in SystemBudget…so also returned is
2006 | 1 | 4.002 | 0.00
2006 | 2 | 4.002 | 0.00
2006 | 3 | 4.002 | 0.00
2006 | 4 | 4.002 | 0.00
2006 | 5 | 4.002 | 0.00
2006 | 6 | 4.002 | 0.00
etc up to 12. Am I going down the right route with a cursor?
Best of what I can think of now is
CREATE TABLE #Budget (BudgetID decimal(9),Year INT,Month INT,Code decimal(9,3),Value float)
CREATE TABLE #SystemBudget (SysBudgetID decimal(9),Code decimal(9,3),SpecialCode varchar(10)) INSERT INTO #Budget SELECT 5, 2006, 3, 4.001, 1023.45
INSERT INTO #SystemBudget SELECT 1, 4.001, 3
UNION ALL SELECT 2, 4.002, 3 SELECT ISNULL(t1.Year, YEAR(GETDATE())), t3.Number, t2.Code, ISNULL(t1.Value, 0)
FROM #Budget t1
RIGHT JOIN #Systembudget t2
ON t1.Code = t2.Code
CROSS JOIN master..spt_values t3
WHERE t3.Type=’P’
AND t3.Number BETWEEN 1 AND 12
ORDER BY t2.Code, t3.Number DROP TABLE #Budget, #SystemBudget
Number Code
———– ———– ———– —————————————————–
2006 1 4.001 1023.45
2006 2 4.001 1023.45
2006 3 4.001 1023.45
2006 4 4.001 1023.45
2006 5 4.001 1023.45
2006 6 4.001 1023.45
2006 7 4.001 1023.45
2006 8 4.001 1023.45
2006 9 4.001 1023.45
2006 10 4.001 1023.45
2006 11 4.001 1023.45
2006 12 4.001 1023.45
2006 1 4.002 0.0
2006 2 4.002 0.0
2006 3 4.002 0.0
2006 4 4.002 0.0
2006 5 4.002 0.0
2006 6 4.002 0.0
2006 7 4.002 0.0
2006 8 4.002 0.0
2006 9 4.002 0.0
2006 10 4.002 0.0
2006 11 4.002 0.0
2006 12 4.002 0.0 (24 row(s) affected) Are both tables really related via the Code column? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Whats happens is, a user enters a budget amount (for a code) for a particular month of a year, and only the data for that month…year…Code combination is stored to the budget table. If no data for another month of the same year..code has been entered then there will only be one row in the Budgets table. In the SystemBudgets table will be a list of codes for which budget data can be entered against and reported for. If there is no data for a code in the Budgets table for a year..month combination then 0 should be returned as the value. Hope this is a bit clearer…?
]]>