DECLARE @Dep DATETIME
SET @dep = '12/24/2009'DECLARE @IsWeekday NVARCHAR(10)
SET @IsWeekday = SUBSTRING(DATENAME(weekday,@dep),1,3)DECLARE @A table
(
Sat bit ,
Sun bit,
Mon bit,
Tue bit,
Wed bit,
Thu bit,
Fri bit
)insert @A(Sat,Sun,Mon,Tue,Wed,Thu,Fri)
select 1,0,0,0,0,0,0
union
select 0,0,1,0,0,0,1
union
select 0,0,0,1,0,0,0
union
select 0,0,0,1,0,0,0select * from @A想根据@IsWeekday来获取相应的数据,例如:
select * from @A A where A.@IsWeekday='1'不能用exec(SQL)那种,用直接select得到,如何实现?
谢谢!
SET @dep = '12/24/2009'DECLARE @IsWeekday NVARCHAR(10)
SET @IsWeekday = SUBSTRING(DATENAME(weekday,@dep),1,3)DECLARE @A table
(
Sat bit ,
Sun bit,
Mon bit,
Tue bit,
Wed bit,
Thu bit,
Fri bit
)insert @A(Sat,Sun,Mon,Tue,Wed,Thu,Fri)
select 1,0,0,0,0,0,0
union
select 0,0,1,0,0,0,1
union
select 0,0,0,1,0,0,0
union
select 0,0,0,1,0,0,0select * from @A
WHERE CASE @IsWeekday
WHEN '星期一' THEN Mon
WHEN '星期二' THEN Tue
WHEN '星期三' THEN Wed
WHEN '星期四' THEN Thu
WHEN '星期五' THEN Fri
WHEN '星期六' THEN Sat
WHEN '星期七' THEN Sun
END='1'
不用动态不好做
where (Sat=1 and @IsWeekday='Sat')
Or (Sun=1 and @IsWeekday='Sun')
Or .....
SET @dep = '12/21/2009'DECLARE @IsWeekday NVARCHAR(10)
SET @IsWeekday = SUBSTRING(DATENAME(weekday,@dep),1,3)
SELECT @IsWeekday
DECLARE @A table
(
Sat bit ,
Sun bit,
Mon bit,
Tue bit,
Wed bit,
Thu bit,
Fri bit
)insert @A(Sat,Sun,Mon,Tue,Wed,Thu,Fri)
select 1,0,0,0,0,0,0
union
select 0,0,1,0,0,0,1
union
select 0,0,0,1,0,0,0
union
select 0,0,0,1,0,0,0SELECT * FROM @a
WHERE
substring(ltrim(Sat)+ltrim(Sun)+ltrim(Mon)+ltrim(Tue)+ltrim(Wed)+ltrim(Thu)+ltrim(Fri) ,
CASE WHEN @IsWeekday ='星期六' THEN 1
WHEN @IsWeekday ='星期日' THEN 2
WHEN @IsWeekday ='星期一' THEN 3
WHEN @IsWeekday ='星期二' THEN 4
WHEN @IsWeekday ='星期三' THEN 5
WHEN @IsWeekday ='星期四' THEN 6
WHEN @IsWeekday ='星期五' THEN 7
END ,1)=1
--result
/*Sat Sun Mon Tue Wed Thu Fri
---- ---- ---- ---- ---- ---- ----
0 0 1 0 0 0 1(所影响的行数为 1 行)*/
union
select null,null,null,null,null,null,null
这样的数据,case when如何写呢?谢谢大家!
union
select null,null,null,null,null,null,null
忘记说了,这样的数据也要的
CASE @IsWeekday
WHEN 'Mon' THEN Mon
WHEN 'Tue' THEN Tue
WHEN 'Wed' THEN Wed
WHEN 'Thu' THEN Thu
WHEN 'Fri' THEN Fri
WHEN 'Sat' THEN Sat
WHEN 'Sun' THEN Sun
END='1'orCASE @IsWeekday
WHEN 'Mon' THEN Mon
WHEN 'Tue' THEN Tue
WHEN 'Wed' THEN Wed
WHEN 'Thu' THEN Thu
WHEN 'Fri' THEN Fri
WHEN 'Sat' THEN Sat
WHEN 'Sun' THEN Sun
END is null应该这样啊
CASE @IsWeekday
WHEN 'Mon' THEN Mon
WHEN 'Tue' THEN Tue
WHEN 'Wed' THEN Wed
WHEN 'Thu' THEN Thu
WHEN 'Fri' THEN Fri
WHEN 'Sat' THEN Sat
WHEN 'Sun' THEN Sun
END IN ('1' ,NULL)