How to declare an array inside an Sql server stored procedure?

Question

I need to declare 12 decimal variables corresponding to each month of the year, with the cursor I summarize the values ​​with these variables, and then update some sales data.

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 can declare a table variable (type variable table declaration):

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

You can add additional columns as you like:

 declare @MonthsSale table(monthnr int, totalsales tinyint) 

You can update the table variable just 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 are not using a table variable and the aggregated SUM operator rather than a cursor? SQL does a great job. In 99.87% of cases when you find yourself using the cursor, there is a more set-oriented alternative:

 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 

Answer 3

T-SQL does not support arrays that I know of.

What is the structure of your table? Perhaps you could design a query that does this instead:

 select month, sum(sales) from sales_table group by month order by month 

Answer 4

Great question and great idea, but in SQL you will need to do this:

For the datetime data type, something like this is

 declare @BeginDate datetime = '1/1/2016', @EndDate datetime = '12/1/2016' create table #months (dates datetime) declare @var datetime = @BeginDate while @var < dateadd(MONTH, +1, @EndDate) Begin insert into #months Values(@var) set @var = Dateadd(MONTH, +1, @var) end 

If you really need numbers, do it -

 create table #numbas (digit int) declare @var int = 1 --your starting digit while @var <= 12 --your ending digit begin insert into #numbas Values(@var) set @var = @var +1 end