语法错误

Question

Create Proc GetDays
@YearNo Int,
@MonthNo Int,
@Date varchar(10)
AS
SET NOCOUNT ON
Declare @FirstDay varchar(10),@LastDay varchar(10)
Begin
Select @Date = @YearNo + '-' + @MonthNo + '-' + '01'
Select @FirstDay = DATEADD(m, DATEDIFF(m,0,@Date), 0)
Select @LastDay = DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@Date)+1, 0))
Select HouseNo,RoomNo,EmpNo,case Days when (datediff(dd,InDate,@FirstDay)/*这错*/ >= 0) and (OutDate is null) then datediff(dd,@FirstDay,@LastDay)
when datediff(dd,InDate,@FirstDay) >= 0 and OutDate is not null then datediff(dd,@FirstDay,OutDate)
when datediff(dd,InDate,@FirstDay) < 0 and OutDate is null then datediff(dd,InDate,@LastDay)
when datediff(dd,InDate,@FirstDay) < 0 and OutDate is not null then datediff(dd,InDate,OutDate)
From TB_EmpRoom Where @YearNo group HouseNo,RoomNo,EmpNo
End;服务器: 消息 170，级别 15，状态 1，过程 GetDays，行 12
第 12 行: '>' 附近有语法错误。

Answer 1

Where @YearNo group 
啥意思?

Answer 2

是不是datediff的参数问题。看看要对比的2个日期是否是日期

Answer 3

case语句末尾还要加 ENDCASE 
WHEN ... THEN .. 
WHEN.. THEN.. 
ELSE..--可选
END

Answer 4

Create Proc GetDays
@YearNo Int,
@MonthNo Int,
@Date varchar(10)
AS
SET NOCOUNT ON
Declare @FirstDay varchar(10),@LastDay varchar(10)
Begin
Select @Date = @YearNo + '-' + @MonthNo + '-' + '01'
Select @FirstDay = DATEADD(m, DATEDIFF(m,0,@Date), 0)
Select @LastDay = DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@Date)+1, 0))
Select HouseNo,RoomNo,EmpNo,case when (datediff(dd,InDate,@FirstDay) >= 0) and (OutDate is null) then datediff(dd,@FirstDay,@LastDay) 
when datediff(dd,InDate,@FirstDay) >= 0 and OutDate is not null then datediff(dd,@FirstDay,OutDate) 
when datediff(dd,InDate,@FirstDay) < 0 and OutDate is null then datediff(dd,InDate,@LastDay) 
when datediff(dd,InDate,@FirstDay) < 0 and OutDate is not null then datediff(dd,InDate,OutDate)
end 
From TB_EmpRoom group by HouseNo,RoomNo,EmpNo,InDate,OutDate
End;
这样创建成功了， 我需要的天数怎么保存呢， 就是每个then 后面的datediff函数取得的结果

Answer 5

如果是保存到表中，先创建目的表，再用INSERT INTO..SELECT..语句插入INSERT INTO table1 SELECT col1,cole2 FROM ....如果是要存储过程返回结果，在存储过程中用OUTPUT参数