sqlserver 存储过程，日期处理，问题记录

GetDate

select DateName(year,GetDate()) as '年';

select GETDATE() as '当前日期',
DateName(year,GetDate()) as '年',
DateName(month,GetDate()) as '月',
DateName(day,GetDate()) as '日',
DateName(dw,GetDate()) as '星期',
DateName(week,GetDate()) as '周数',
DateName(hour,GetDate()) as '时',
DateName(minute,GetDate()) as '分',
DateName(second,GetDate()) as '秒'

格式转换可以参考：

SQL Server中使用convert进行日期转换 - xfyn - 博客园 (cnblogs.com)

#存储过程遇到问题

##1.出现总是无法找到存储过程问题：

ALTER PROCEDURE [dbo].[getOperDataDepMonth]
  --@passengerFlow AS varchar =0 ,
  --@income AS decimal =0 ,
  --@energyCost AS decimal =0 ,
  --@materialCost AS decimal ,
  --@month AS varchar, 
    
    @monthCount as int = 1,
    @monthNext AS date ='20210101'  --当前月份 yyyymmdd
AS
BEGIN
DECLARE
    @flowCount int,
    @tmpTableName VARCHAR,
    @sql VARCHAR,
    @sel VARCHAR,
    @date VARCHAR
    WHILE @monthCount <= 12 
    BEGIN
        set @sel = ' SELECT sum(cast(num as INT) + cast(num1 as INT) ) FROM '
        set @monthCount = @monthCount + 1
        set @monthNext = dateadd(mm,1,@monthNext)
        set @date = '202111'
        set @tmpTableName = ' SiteTrafficM'+@date
        exec(@sel+ @tmpTableName)
    END

END

发现只要变量前面加上空格就好了，原因未知，大概是 sql语句的拼接问题吧，另外，varchar必须给长度varchar(100),而且尽量值要打，否则sql会被截取

##发现@sel总是无法执行

总是提示：

SQL存储过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@statement'

经过百度：

 

 

##关于使用sp_executesql问题

参考：sp_executesql介绍和使用 - 万德源 - 博客园 (cnblogs.com) 

并且介绍了如何将查出的字段，赋值给变量取出，

 

此时sql已经修改：

ALTER PROCEDURE [dbo].[getOperDataDepMonth]
  --@passengerFlow AS varchar =0 ,
  --@income AS decimal =0 ,
  --@energyCost AS decimal =0 ,
  --@materialCost AS decimal ,
  --@month AS varchar, 
    @monthCount as int = 1,
    @monthNext AS date ='2021-02-03'  --当前月份 yyyy-mm-dd
AS
BEGIN
DECLARE
    @flowCount int,
    @tmpTableName NVARCHAR(1000),
    @sql VARCHAR(1000),
    @selKl NVARCHAR(1000), --查询客流sql语句
    @selXianJin NVARCHAR(1000), --查询现金sql语句
    @date VARCHAR(1000),
    @tmp VARCHAR(1000)
    WHILE @monthCount < 12 
    BEGIN
        --日期转换为yyyymmdd
        set @date = left(replace(@monthNext,'-',''),6)
        --查询每月客流量
        set @tmpTableName = 'pdd.[dbo].SiteTrafficM'+@date
        set @flowCount = 0
        set @selKl = N'SELECT @count = sum(cast(Get_on_the_bus_passengers as INT) + cast(Get_off_the_bus_passengers as INT) ) FROM '+@tmpTableName 
        exec sp_executesql @selKl, N'@count int out', @flowCount out
        
        --查询每月现金收入
        --set @tmpTableName = 'pdd.[dbo].SiteTrafficM'
        --@selXianJin
        
        
        --月份递增
        set @monthNext = dateadd(mm,1,@monthNext)
        set @monthCount = @monthCount + 1
        print @monthCount

        PRINT @flowCount
    END
END