如何在 MSSQLServer 存储过程中声明数组？

我需要声明12个十进制变量，对应于每个月的年份，用一个游标 I 和这些变量的值，然后更新一些销售信息。

我不知道 sql 服务器是否有这种语法

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

这个密码可以用，好的！

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
515748 次浏览
小开

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
小开
最佳答案

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
小开

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.


提交答案