English 中文(简体)
How to declare an array inside MS SQL Server Stored Procedure?
原标题:

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
最佳回答

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
问题回答

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

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

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 < 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 <= 12        --your ending digit
    begin
        insert into #numbas Values(@var)
        set @var = @var +1
    end

You can declare an array using the VALUES keyword. Your example can be expressed succintly in the following form:

SELECT * FROM (VALUES 
    (1,100), 
    (2,200), 
    (3,300), 
    (4,400), 
    (5,500), 
    (6,600), 
    (7,700), 
    (8,800), 
    (9,900), 
    (10,1000), 
    (11,1100), 
    (12,1200)
) MonthSale (monthnr, Amount)

Feel free to INSERT it into a table or place a WHERE or GROUP condition for your purposes.





相关问题
Performance impact of indexed view in MS SQL Server 2008

Does anyone have experience with using indexed view in MS SQL Server 2008? I am trying to find out how does indexed view affect performance of insert / update statements, that are adding / updating ...

Lock Escalation - What s happening here?

While altering a table (removing a column) in SQL Server 2008, I clicked the Generate Change Script button and I noticed that the change script it generated drops the column, says "go" and then runs ...

Round to nearest 5 in SQL Server

I have a Money column in my SQL Server 2008 table. In my below query how can I round it to nearest 5$ select FineAmount from tickets Thanks

热门标签