How to declare an array inside MS SQL Server Stored Procedure?

Question

I need to declare 12 decimal variables, corresponding to each month's year, with a cursor I sum values to this variables, then later I Update some sales information.

I don't know if sql server has this syntax

 Declare MonthsSale(1 to 12) as decimal(18,2) 

This code works Ok. !

CREATE PROCEDURE [dbo].[proc_test] AS BEGIN  --SET NOCOUNT ON;  DECLARE @monthsales TABLE ( monthnr int,    amount decimal(18,2)    )   -- PUT YOUR OWN CODE HERE   -- THIS IS TEST CODE -- 1 REPRESENTS JANUARY, ... INSERT @monthsales (monthnr, amount) VALUES (1, 100) INSERT @monthsales (monthnr, amount) VALUES (1, 100)  INSERT @monthsales (monthnr, amount) VALUES (2, 200) INSERT @monthsales (monthnr, amount) VALUES (3, 300) INSERT @monthsales (monthnr, amount) VALUES (4, 400) INSERT @monthsales (monthnr, amount) VALUES (5, 500) INSERT @monthsales (monthnr, amount) VALUES (6, 600) INSERT @monthsales (monthnr, amount) VALUES (7, 700) INSERT @monthsales (monthnr, amount) VALUES (8, 800) INSERT @monthsales (monthnr, amount) VALUES (9, 900) INSERT @monthsales (monthnr, amount) VALUES (10, 1000) INSERT @monthsales (monthnr, amount) VALUES (11, 1100) INSERT @monthsales (monthnr, amount) VALUES (12, 1200)   SELECT monthnr, SUM(amount) AS SUM_MONTH_1 FROM @monthsales WHERE monthnr = 1 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_2 FROM @monthsales WHERE monthnr = 2 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_3 FROM @monthsales WHERE monthnr = 3 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_4 FROM @monthsales WHERE monthnr = 4 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_5 FROM @monthsales WHERE monthnr = 5 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_6 FROM @monthsales WHERE monthnr = 6 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_7 FROM @monthsales WHERE monthnr = 7 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_8 FROM @monthsales WHERE monthnr = 8 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_9 FROM @monthsales WHERE monthnr = 9 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_10 FROM @monthsales WHERE monthnr = 10 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_11 FROM @monthsales WHERE monthnr = 11 GROUP BY monthnr SELECT monthnr, SUM(amount) AS SUM_MONTH_12 FROM @monthsales WHERE monthnr = 12 GROUP BY monthnr  -- END TEST CODE END 

Answer 1

You could declare a table variable (Declaring a variable of type table):

declare @MonthsSale table(monthnr int) insert into @MonthsSale (monthnr) values (1) insert into @MonthsSale (monthnr) values (2) .... 

You can add extra columns as you like:

declare @MonthsSale table(monthnr int, totalsales tinyint) 

You can update the table variable like any other table:

update m set m.TotalSales = sum(s.SalesValue) from @MonthsSale m left join Sales s on month(s.SalesDt) = m.MonthNr 

Answer 2

Is there a reason why you aren't using a table variable and the aggregate SUM operator, instead of a cursor? SQL excels at set-oriented operations. 99.87% of the time that you find yourself using a cursor, there's a set-oriented alternative that's more efficient:

declare @MonthsSale table ( MonthNumber int, MonthName varchar(9), MonthSale decimal(18,2) )  insert into @MonthsSale select     1, 'January', 100.00 union select         2, 'February', 200.00 union select         3, 'March', 300.00 union select         4, 'April', 400.00 union select         5, 'May', 500.00 union select         6, 'June', 600.00 union select         7, 'July', 700.00 union select         8, 'August', 800.00 union select         9, 'September', 900.00 union select         10, 'October', 1000.00 union select         11, 'November', 1100.00 union select         12, 'December', 1200.00  select * from @MonthsSale    select SUM(MonthSale) as [TotalSales] from @MonthsSale