Question

sql 存储过程中 用case when 替代if else

我需要把：
Create PROCEDURE dbo.sp_getStatisticInfo( @ID nvarchar(50) )
AS
if(@ID='1')
begin
select * from tb_dailyCheck where f_startTime between @StartTime and dateadd(day,1,@EndTime)
end
else if(@ID='2')
begin
select * from tb_shiftCheck where f_startTime between @StartTime and dateadd(day,1,@EndTime)
end
else
begin
select * from tb_weeklyM where f_startTime between @StartTime and dateadd(day,1,@EndTime)
end
改成这样：

Create PROCEDURE dbo.sp_getStatisticInfo( @ID nvarchar(50) )
AS
Case @ID
when '1' then
begin
select * from tb_dailyCheck where f_startTime between @StartTime and dateadd(day,1,@EndTime)
end
when '2' then
begin
select * from tb_shiftCheck where f_startTime between @StartTime and dateadd(day,1,@EndTime)
end
else
begin
select * from tb_weeklyM where f_startTime between @StartTime and dateadd(day,1,@EndTime)
end
前一段是可以的，但是后一段运行的时候总是语法错误，望大虾指点啊:-)
比如下面这段，也是跑不起来的：
Create procedure Test1(@Name varchar(50))
As

Case When @Name='s' Then (print 'DName')
When @Name='d' Then (print 'did')
Else (print 'dids')
End
去掉小括号同样跑不起来
望大虾指点一二。。。

Answer 1

SQL中的case when 只能用在查询语句中,不能单独使用,由于每个条件查询的表都不一样,改写不能

Answer 2

不是太懂sqlserver的procedures，但是看你用case when写的这一段，貌似最后缺少一个end吧
case when的最后应该有esle ... end 吧。

Answer 3

case 字段when 值 then else 值end