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

User · Answer

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

User · Answer

T-SQL doesn t support arrays that I m aware of    What s your table structure  You could probably design a query that does this instead   select month  sum sales  from sales table group by month order by month

User · Answer

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

User · Answer

Great question and great idea  but in SQL you ll need to do this   For data type datetime  something like this-  declare  BeginDate    datetime    1 1 2016            EndDate      datetime    12 1 2016  create table  months  dates datetime  declare  var datetime    BeginDate    while  var  lt  dateadd MONTH   1   EndDate     Begin           insert into  months Values  var            set  var   Dateadd MONTH   1   var     end   If all you really want is numbers  do this-  create table  numbas  digit int  declare  var int   1        --your starting digit     while  var  lt   12        --your ending digit     begin         insert into  numbas Values  var          set  var    var  1     end

[sql-server-2008] How to declare an array inside MS SQL Server Stored Procedure?

Examples related to sql-server-2008

Examples related to stored-procedures