学习动态SQL

Question

我正在努力学习动态SQL。

我已经建立了一个基本的例子，我被困住了，我不明白为什么@Month变量不会在每个循环中更新。

下面是我所做的例子：

declare @M int = 1
declare @SQL varchar(max) = ''
declare @Cnt int = 1
declare @Month varchar(25) = (datename(m, '2016-' + convert(varchar(2), @M) + '-01'))

while @Cnt <= 12
begin
    set @SQL = @SQL + 'select ' + convert(varchar(25), @M) + ' as M, ''' + @Month + ''' as Month'
    if  @Cnt <> 12 set @SQL = @SQL + ' Union All '  
    set @Cnt = @Cnt + 1
    set @M = @M + 1
end

 exec  (@SQL)

结果：

M   Month
1   January
2   January
3   January
4   January
5   January
6   January
7   January
8   January
9   January
10  January
11  January
12  January

我希望从1月到12月生成MonthName

为什么月份名称不更新每个循环？快把我逼疯了，我等不及明天上班去问老板。今晚需要睡觉。

非常感谢。

Answer 1

您使用的是@m，而不是月份的名称：

declare @M int = 0
declare @SQL varchar(max) = ''
declare @Cnt int = 1
declare @Month varchar(25) 

while @Cnt <= 12
begin
    set @M = @M + 1
    set @Month = datename(m, '2016-' + convert(varchar(2), @M) + '-01')
    set @SQL = @SQL + 'select ' + convert(varchar(25), @M) + ' as M, ''' + @Month + ''' as Month'
    if  @Cnt <> 12 set @SQL = @SQL + ' Union All '  
    set @Cnt = @Cnt + 1



end

 exec  (@SQL)

Answer 2

您需要在循环中每次添加日期，就像@M变量一样。取另一个变量作为日期。

DECLARE @SQL varchar(max) = ''
DECLARE @Cnt int = 1
DECLARE @TempDate DateTime = Cast('2016-03-01' As DateTime)

然后在while循环中添加月份

WHILE @Cnt <= 12
BEGIN
    set @SQL = @SQL + 'select ' + CAST(MONTH(@TempDate) AS VARCHAR(2)) + ' as M, ''' + DateName(m,@TempDate) + ''' as Month '
    IF  @Cnt <> 12 SET @SQL = @SQL + ' Union All ' 
    SET @TempDate = DateAdd(m, @Cnt, @TempDate)
    SET @Cnt = @Cnt +1
END

EXEC  (@SQL)

Answer 3

您没有在里面使用@Month，下面是您要查找的代码：

DECLARE @M INT = 1
DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @Cnt INT = 1
DECLARE @Month VARCHAR(25)

WHILE @Cnt <= 12
    BEGIN
        SET @Month = ( DATENAME(m, '2016-' + CONVERT(VARCHAR(2), @M) + '-01') )

        SET @SQL = @SQL + 'select ' + CONVERT(VARCHAR(25), @M) + ' as M, ''' + @Month + ''' as Month'
        IF @Cnt <> 12
            SET @SQL = @SQL + ' Union All '  



        SET @Cnt = @Cnt + 1
        SET @M = @M + 1
    END

EXEC  (@SQL)