DECLARE @str varchar(1000) DECLARE @i int
SET @i = DAY
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()), 102)))
SET @str = (CASE WHEN @i = 1 THEN 'd_1' WHEN @i = 2 THEN 'd_2' WHEN @i = 3 THEN 'd_3'
WHEN @i = 4 THEN 'd_4' WHEN @i = 5 THEN 'd_5' WHEN @i = 6 THEN 'd_6' WHEN @i
= 7 THEN 'd_7' WHEN @i = 8 THEN 'd_8' WHEN @i = 9 THEN 'd_9' WHEN @i = 10 THEN
'd_10' WHEN @i = 11 THEN 'd_11' WHEN @i = 12 THEN 'd_12' WHEN @i = 13 THEN 'd_13'
WHEN @i = 14 THEN 'd_14' WHEN @i = 15 THEN 'd_15' WHEN @i = 16 THEN 'd_16' WHEN
@i = 17 THEN 'd_17' WHEN @i = 18 THEN 'd_18' WHEN @i = 19 THEN 'd_19' WHEN @i
= 20 THEN 'd_20' WHEN @i = 21 THEN 'd_21' WHEN @i = 22 THEN 'd_22' WHEN @i =
23 THEN 'd_23' WHEN @i = 24 THEN 'd_24' WHEN @i = 25 THEN 'd_25' WHEN @i = 26
THEN 'd_26' WHEN @i = 27 THEN 'd_27' WHEN @i = 28 THEN 'd_28' WHEN @i = 29 THEN
'd_29' WHEN @i = 30 THEN 'd_30' WHEN @i = 31 THEN 'd_31' END)
SET @str = 'update rytj_record set ' + @str + ' = rytj.target_ry FROM rytj_record,
(SELECT depts.deptid, users.userid, YEAR
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AS year_ry, MONTH
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AS month_ry, DAY
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AS day_ry, (CASE WHEN tj_time IS NULL
THEN NULL ELSE 1 END) AS target_ry
FROM users INNER JOIN
depts ON users.deptid = depts.deptid LEFT OUTER JOIN
jb_record ON users.userid = jb_record.userid AND year(tj_time)
= YEAR
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AND month(tj_time) = MONTH
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AND day(tj_time) = DAY
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102)))
WHERE (depts.deptid = 6) OR
(depts.deptid = 20) OR
(depts.deptid = 21) OR
(depts.deptid = 22) OR
(depts.deptid = 23) OR
(depts.deptid = 24) OR
(depts.deptid = 30) OR
(depts.deptid = 31) OR
(depts.deptid = 32) OR
(depts.deptid = 33) OR
(depts.deptid = 40) OR
(depts.deptid = 41) OR
(depts.deptid = 42) OR
(depts.deptid = 11)) AS rytj
WHERE rytj_record.userid = rytj.userid AND
rytj_record.year_ry = rytj.year_ry AND
rytj_record.month_ry = rytj.month_ry'
EXEC (@str)
这句话影响行数始终是1,不知道哪里写得不对。我去掉参数,即把@str换成d_23执行结果却是正确,求助啊
SET @i = DAY
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()), 102)))
SET @str = (CASE WHEN @i = 1 THEN 'd_1' WHEN @i = 2 THEN 'd_2' WHEN @i = 3 THEN 'd_3'
WHEN @i = 4 THEN 'd_4' WHEN @i = 5 THEN 'd_5' WHEN @i = 6 THEN 'd_6' WHEN @i
= 7 THEN 'd_7' WHEN @i = 8 THEN 'd_8' WHEN @i = 9 THEN 'd_9' WHEN @i = 10 THEN
'd_10' WHEN @i = 11 THEN 'd_11' WHEN @i = 12 THEN 'd_12' WHEN @i = 13 THEN 'd_13'
WHEN @i = 14 THEN 'd_14' WHEN @i = 15 THEN 'd_15' WHEN @i = 16 THEN 'd_16' WHEN
@i = 17 THEN 'd_17' WHEN @i = 18 THEN 'd_18' WHEN @i = 19 THEN 'd_19' WHEN @i
= 20 THEN 'd_20' WHEN @i = 21 THEN 'd_21' WHEN @i = 22 THEN 'd_22' WHEN @i =
23 THEN 'd_23' WHEN @i = 24 THEN 'd_24' WHEN @i = 25 THEN 'd_25' WHEN @i = 26
THEN 'd_26' WHEN @i = 27 THEN 'd_27' WHEN @i = 28 THEN 'd_28' WHEN @i = 29 THEN
'd_29' WHEN @i = 30 THEN 'd_30' WHEN @i = 31 THEN 'd_31' END)
SET @str = 'update rytj_record set ' + @str + ' = rytj.target_ry FROM rytj_record,
(SELECT depts.deptid, users.userid, YEAR
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AS year_ry, MONTH
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AS month_ry, DAY
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AS day_ry, (CASE WHEN tj_time IS NULL
THEN NULL ELSE 1 END) AS target_ry
FROM users INNER JOIN
depts ON users.deptid = depts.deptid LEFT OUTER JOIN
jb_record ON users.userid = jb_record.userid AND year(tj_time)
= YEAR
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AND month(tj_time) = MONTH
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AND day(tj_time) = DAY
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102)))
WHERE (depts.deptid = 6) OR
(depts.deptid = 20) OR
(depts.deptid = 21) OR
(depts.deptid = 22) OR
(depts.deptid = 23) OR
(depts.deptid = 24) OR
(depts.deptid = 30) OR
(depts.deptid = 31) OR
(depts.deptid = 32) OR
(depts.deptid = 33) OR
(depts.deptid = 40) OR
(depts.deptid = 41) OR
(depts.deptid = 42) OR
(depts.deptid = 11)) AS rytj
WHERE rytj_record.userid = rytj.userid AND
rytj_record.year_ry = rytj.year_ry AND
rytj_record.month_ry = rytj.month_ry'
EXEC (@str)
这句话影响行数始终是1,不知道哪里写得不对。我去掉参数,即把@str换成d_23执行结果却是正确,求助啊
EXEC (@str)
然后再执行动态sql
exec(@str)
(SELECT depts.deptid, users.userid, YEAR
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AS year_ry, MONTH
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AS month_ry, DAY
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AS day_ry, (CASE WHEN tj_time IS NULL
THEN NULL ELSE 1 END) AS target_ry
FROM users INNER JOIN
depts ON users.deptid = depts.deptid LEFT OUTER JOIN
jb_record ON users.userid = jb_record.userid AND year(tj_time)
= YEAR
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AND month(tj_time) = MONTH
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102))) AND day(tj_time) = DAY
((SELECT CONVERT(varchar(100), dateadd(day, - 1, getdate()),
102)))
WHERE (depts.deptid = 6) OR
(depts.deptid = 20) OR
(depts.deptid = 21) OR
(depts.deptid = 22) OR
(depts.deptid = 23) OR
(depts.deptid = 24) OR
(depts.deptid = 30) OR
(depts.deptid = 31) OR
(depts.deptid = 32) OR
(depts.deptid = 33) OR
(depts.deptid = 40) OR
(depts.depti
服务器: 消息 170,级别 15,状态 1,行 31
第 31 行: 'depti' 附近有语法错误。这也没错啊