How to assign the multiple variable based on condition in sql server 2008R2? I am able to do it by accessing the table separately for each variable, but I want to do it in a single statement. Here is the code I written it in a single statement but as CASE expression returns only single value.
如何根据sql server 2008R2中的条件分配多个变量？我可以通过分别访问每个变量的表来完成它，但我想在一个语句中完成它。这是我在单个语句中编写的代码，但CASE表达式只返回单个值。
SELECT
@Sun_ST = CASE WHEN (isnull(@Sun_ShiftCode,'') <> '') AND Std.Shift_Code = @Sun_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.Start_Hour))) + convert(varchar,Std.Start_Hour) + replicate('0',2-len(convert(varchar,Std.Start_Minute))) + convert(varchar,Std.Start_Minute) END,
@Sun_ET = CASE WHEN (isnull(@Sun_ShiftCode,'') <> '') AND Std.Shift_Code = @Sun_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.End_Hour))) + convert(varchar,Std.End_Hour) + replicate('0',2-len(convert(varchar,Std.End_Minute))) + convert(varchar,Std.End_Minute) END,
@Mon_ST = CASE WHEN (isnull(@Mon_ShiftCode,'') <> '') AND Std.Shift_Code = @Mon_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.Start_Hour))) + convert(varchar,Std.Start_Hour) + replicate('0',2-len(convert(varchar,Std.Start_Minute))) + convert(varchar,Std.Start_Minute)END,
@Mon_ET = CASE WHEN (isnull(@Mon_ShiftCode,'') <> '') AND Std.Shift_Code = @Mon_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.End_Hour))) + convert(varchar,Std.End_Hour) + replicate('0',2-len(convert(varchar,Std.End_Minute))) + convert(varchar,Std.End_Minute) END,
@Tue_ST = CASE WHEN (isnull(@Tue_ShiftCode,'') <> '') AND Std.Shift_Code = @Tue_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.Start_Hour))) + convert(varchar,Std.Start_Hour) + replicate('0',2-len(convert(varchar,Std.Start_Minute))) + convert(varchar,Std.Start_Minute) END,
@Tue_ET = CASE WHEN (isnull(@Tue_ShiftCode,'') <> '') AND Std.Shift_Code = @Tue_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.End_Hour))) + convert(varchar,Std.End_Hour) + replicate('0',2-len(convert(varchar,Std.End_Minute))) + convert(varchar,Std.End_Minute) END,
@Wed_ST = CASE WHEN (isnull(@Wed_ShiftCode,'') <> '') AND Std.Shift_Code = @Wed_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.Start_Hour))) + convert(varchar,Std.Start_Hour) + replicate('0',2-len(convert(varchar,Std.Start_Minute))) + convert(varchar,Std.Start_Minute) END,
@Wed_ET = CASE WHEN (isnull(@Wed_ShiftCode,'') <> '') AND Std.Shift_Code = @Wed_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.End_Hour))) + convert(varchar,Std.End_Hour) + replicate('0',2-len(convert(varchar,Std.End_Minute))) + convert(varchar,Std.End_Minute)END,
@Thu_ST = CASE WHEN (isnull(@Thu_ShiftCode,'') <> '') AND Std.Shift_Code = @Thu_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.Start_Hour))) + convert(varchar,Std.Start_Hour) + replicate('0',2-len(convert(varchar,Std.Start_Minute))) + convert(varchar,Std.Start_Minute) END,
@Thu_ET = CASE WHEN (isnull(@Thu_ShiftCode,'') <> '') AND Std.Shift_Code = @Thu_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.End_Hour))) + convert(varchar,Std.End_Hour) + replicate('0',2-len(convert(varchar,Std.End_Minute))) + convert(varchar,Std.End_Minute) END,
@Fri_ST = CASE WHEN (isnull(@Fri_ShiftCode,'') <> '') AND Std.Shift_Code = @Fri_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.Start_Hour))) + convert(varchar,Std.Start_Hour) + replicate('0',2-len(convert(varchar,Std.Start_Minute))) + convert(varchar,Std.Start_Minute) END,
@Fri_ET = CASE WHEN (isnull(@Fri_ShiftCode,'') <> '') AND Std.Shift_Code = @Fri_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.End_Hour))) + convert(varchar,Std.End_Hour) + replicate('0',2-len(convert(varchar,Std.End_Minute))) + convert(varchar,Std.End_Minute) END,
@Sat_ST = CASE WHEN (isnull(@Sat_ShiftCode,'') <> '') AND Std.Shift_Code = @Sat_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.Start_Hour))) + convert(varchar,Std.Start_Hour) + replicate('0',2-len(convert(varchar,Std.Start_Minute))) + convert(varchar,Std.Start_Minute) END,
@Sat_ET = CASE WHEN (isnull(@Sat_ShiftCode,'') <> '') AND Std.Shift_Code = @Sat_ShiftCode THEN replicate('0',2-len(convert(varchar,Std.End_Hour))) + convert(varchar,Std.End_Hour) + replicate('0',2-len(convert(varchar,Std.End_Minute))) + convert(varchar,Std.End_Minute) END
FROM dbo.Table Std
WHERE Std.Country_Code = @Country_Code
1 个解决方案
#1
0
Is this what you are looking for?
这是你想要的？
declare @Sun_ShiftCode int=1
declare @Mon_ShiftCode int=2
declare @Tue_ShiftCode int=3
declare @Wed_ShiftCode int=4
declare @Thu_ShiftCode int=5
declare @Fri_ShiftCode int=6
declare @Sat_ShiftCode int=7
declare @Sun_ST varchar(10);
declare @Sun_ET varchar(10);
declare @Mon_ST varchar(10);
declare @Mon_ET varchar(10);
declare @Tue_ST varchar(10);
declare @Tue_ET varchar(10);
declare @Wed_ST varchar(10);
declare @Wed_ET varchar(10);
declare @Thu_ST varchar(10);
declare @Thu_ET varchar(10);
declare @Fri_ST varchar(10);
declare @Fri_ET varchar(10);
declare @Sat_ST varchar(10);
declare @Sat_ET varchar(10);
declare @country_code int=1
;with Tab as
(
select
*
from
(values
(1,'8','0','17','0',1)
,(2,'8','5','16','50',1)
,(3,'8','10','16','40',1)
,(4,'8','15','16','30',1)
,(5,'8','20','16','20',1)
,(6,'8','25','16','10',1)
,(7,'8','30','16','0',1)
)a(Shift_Code,Start_Hour,Start_Minute,End_Hour,End_Minute,Country_Code)
)
select
@Sun_ST=max(case when Shift_Code=@Sun_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Sun_ET=max(case when Shift_Code=@Sun_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Mon_ST=max(case when Shift_Code=@Mon_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Mon_ET=max(case when Shift_Code=@Mon_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Tue_ST=max(case when Shift_Code=@Tue_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Tue_ET=max(case when Shift_Code=@Tue_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Wed_ST=max(case when Shift_Code=@Wed_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Wed_ET=max(case when Shift_Code=@Wed_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Thu_ST=max(case when Shift_Code=@Thu_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Thu_ET=max(case when Shift_Code=@Thu_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Fri_ST=max(case when Shift_Code=@Fri_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Fri_ET=max(case when Shift_Code=@Fri_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Sat_ST=max(case when Shift_Code=@Sat_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Sat_ET=max(case when Shift_Code=@Sat_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
from tab Std
WHERE Std.Country_Code = @Country_Code
and std.Shift_Code is not null
select @Sun_ST Sun_ST
, @Sun_ET Sun_ET
, @Mon_ST Mon_ST
, @Mon_ET Mon_ET
, @Tue_ST Tue_ST
, @Tue_ET Tue_ET
, @Wed_ST Wed_ST
, @Wed_ET Wed_ET
, @Thu_ST Thu_ST
, @Thu_ET Thu_ET
, @Fri_ST Fri_ST
, @Fri_ET Fri_ET
, @Sat_ST Sat_ST
, @Sat_ET Sat_ET
#1
0
Is this what you are looking for?
这是你想要的？
declare @Sun_ShiftCode int=1
declare @Mon_ShiftCode int=2
declare @Tue_ShiftCode int=3
declare @Wed_ShiftCode int=4
declare @Thu_ShiftCode int=5
declare @Fri_ShiftCode int=6
declare @Sat_ShiftCode int=7
declare @Sun_ST varchar(10);
declare @Sun_ET varchar(10);
declare @Mon_ST varchar(10);
declare @Mon_ET varchar(10);
declare @Tue_ST varchar(10);
declare @Tue_ET varchar(10);
declare @Wed_ST varchar(10);
declare @Wed_ET varchar(10);
declare @Thu_ST varchar(10);
declare @Thu_ET varchar(10);
declare @Fri_ST varchar(10);
declare @Fri_ET varchar(10);
declare @Sat_ST varchar(10);
declare @Sat_ET varchar(10);
declare @country_code int=1
;with Tab as
(
select
*
from
(values
(1,'8','0','17','0',1)
,(2,'8','5','16','50',1)
,(3,'8','10','16','40',1)
,(4,'8','15','16','30',1)
,(5,'8','20','16','20',1)
,(6,'8','25','16','10',1)
,(7,'8','30','16','0',1)
)a(Shift_Code,Start_Hour,Start_Minute,End_Hour,End_Minute,Country_Code)
)
select
@Sun_ST=max(case when Shift_Code=@Sun_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Sun_ET=max(case when Shift_Code=@Sun_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Mon_ST=max(case when Shift_Code=@Mon_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Mon_ET=max(case when Shift_Code=@Mon_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Tue_ST=max(case when Shift_Code=@Tue_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Tue_ET=max(case when Shift_Code=@Tue_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Wed_ST=max(case when Shift_Code=@Wed_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Wed_ET=max(case when Shift_Code=@Wed_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Thu_ST=max(case when Shift_Code=@Thu_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Thu_ET=max(case when Shift_Code=@Thu_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Fri_ST=max(case when Shift_Code=@Fri_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Fri_ET=max(case when Shift_Code=@Fri_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
,@Sat_ST=max(case when Shift_Code=@Sat_ShiftCode then right('00'+Std.Start_Hour,2)+right('00'+Std.Start_Minute,2) else '' end)
,@Sat_ET=max(case when Shift_Code=@Sat_ShiftCode then right('00'+Std.End_Hour,2) +right('00'+Std.End_Minute,2) else '' end)
from tab Std
WHERE Std.Country_Code = @Country_Code
and std.Shift_Code is not null
select @Sun_ST Sun_ST
, @Sun_ET Sun_ET
, @Mon_ST Mon_ST
, @Mon_ET Mon_ET
, @Tue_ST Tue_ST
, @Tue_ET Tue_ET
, @Wed_ST Wed_ST
, @Wed_ET Wed_ET
, @Thu_ST Thu_ST
, @Thu_ET Thu_ET
, @Fri_ST Fri_ST
, @Fri_ET Fri_ET
, @Sat_ST Sat_ST
, @Sat_ET Sat_ET