SQL语句case when then 可以输出变量吗？

[Nick_Ngai]

SQL语句case staid when 'SZ01' then 'aa'可以输出变量吗？
就是再THEN 后面可以输出变量吗？
加入我之前定义了变量
DECLARE @dt DATETIME
SET @dt='2011-09-21';

我想then 后输出@dt,或者在复杂点@dt<->DATEADD(m,-1,@dt)这样就会显示2011-08-21<->2011-09-21

[三断笛]

可以
回复内容太短了！

[-晴天]

当然可以.

[Nick_Ngai]

应该怎样写？

[-晴天]

DECLARE @dt DATETIME,@dt2 datetime
SET @dt='2011-09-21';
set @dt2='2011-12-30'
select (case month(GETDATE()) when 10 then @dt else @dt2 end)
/*

-----------------------
2011-12-30 00:00:00.000

(1 行受影响)

*/

[快溜]

完全可以。

[-晴天]

create table tb([staid] varchar(10))
insert into tb select 'SZ01'
insert into tb select 'SZ02'
go
DECLARE @dt DATETIME,@dt2 datetime
SET @dt='2011-09-21';
set @dt2='2011-12-30'
select (case [staid] when 'SZ01' then @dt else @dt2 end) from tb
/*
-----------------------
2011-09-21 00:00:00.000
2011-12-30 00:00:00.000

(2 行受影响)

*/

[Nick_Ngai]

本来是这样：
CASE Staff_Status WHEN 'PT' THEN '21/08-31/08'

我想把THEN后换成变量@dt='2011-09-21'
CASE Staff_Status WHEN 'PT' THEN @dt类似这样

[Nick_Ngai]

我那直接给@dt报错的

[damo_baby]

可以，如果要复杂可以使用动态SQL

[-晴天]

[Quote=引用 8 楼 anboat 的回复:]
我那直接给@dt报错的
[/Quote]

见 6 楼.

[Nick_Ngai]

DECLARE @dt DATETIME
SET @dt='2011-09-21';

--1.如果本月(9)和上个月(8)状态都为PT，本月(9)份累加方式是21/8-20/9
Select
a.Staff_ID,
SUM(a.Confirmed_Hours) AS Hours,
COUNT(a.Day_Type) AS Leave,
(case
(select top 1 Staff_Status FROM Attendance_Reports c where a.Staff_ID = c.Staff_ID
and c.EffectiveDate >= DATEADD(m,-1,@dt) and c.EffectiveDate < @dt
order by c.EffectiveDate asc)
when 'PT' then '21/08-20/09' end) as Date
from dbo.Attendance_Reports AS a
WHERE (SELECT top 1 Staff_Status FROM dbo.Attendance_Reports b WHERE b.Staff_ID=a.Staff_ID
AND b.EffectiveDate>=DATEADD(m,-1,@dt) AND b.EffectiveDate<@dt order by c.EffectiveDate asc)='PT'
AND a.Staff_Status='PT'
AND a.EffectiveDate >= DATEADD(m,-1,@dt) AND a.EffectiveDate<@dt
AND a.Day_Type NOT IN ('XX') AND Day_Type NOT in ('')
GROUP BY a.Staff_ID

我上面这样写可以，但是把then后面换成变量 @dt形式 就提示错误了

[中国风]

出什么错？

[Nick_Ngai]

DECLARE @dt DATETIME
SET @dt='2011-09-21';

--1.如果本月(9)和上个月(8)状态都为PT，本月(9)份累加方式是21/8-20/9
Select
a.Staff_ID,
SUM(a.Confirmed_Hours) AS Hours,
COUNT(a.Day_Type) AS Leave,
(case
(select top 1 Staff_Status FROM Attendance_Reports c where a.Staff_ID = c.Staff_ID
and c.EffectiveDate >= DATEADD(m,-1,@dt) and c.EffectiveDate < @dt
order by c.EffectiveDate asc)
when 'PT' then '21/08-20/09' end) as Date
from dbo.Attendance_Reports AS a
WHERE (SELECT top 1 Staff_Status FROM dbo.Attendance_Reports b WHERE b.Staff_ID=a.Staff_ID
AND b.EffectiveDate>=DATEADD(m,-1,@dt) AND b.EffectiveDate<@dt order by b.EffectiveDate asc)='PT'
AND a.EffectiveDate >= DATEADD(m,-1,@dt) AND a.EffectiveDate<@dt
AND a.Day_Type NOT IN ('XX') AND Day_Type NOT in ('')
GROUP BY a.Staff_ID

我上面这样写可以，但是把then后面换成变量 @dt形式 就提示错误了

[Nick_Ngai]

sorry!我现在在家里试不了出什么错，家里用的是Oracle数据库，我在这SQL语句是在公司用sql server写的，我也记不清出什么错，那块不用变量就好用

[中国风]

convert(varchar(10),@dt,120)--在then改为这样试试

[Nick_Ngai]

好，我明天早上试试，谢谢你 roy_88！

[Nick_Ngai]

谢谢 roy_88,谢谢大家，可以了，如果我再我想扩充下，then后写@dt<->(@dt-DAY(@dt)+1).
这样是不可以的啊？就是有多个变量

消息 156，级别 15，状态 1，第 16 行
Incorrect syntax near the keyword 'and'.
消息 156，级别 15，状态 1，第 19 行
Incorrect syntax near the keyword 'AS'.
消息 156，级别 15，状态 1，第 22 行
Incorrect syntax near the keyword 'AS'.
消息 156，级别 15，状态 1，第 25 行
Incorrect syntax near the keyword 'ORDER'.