if exists (select top 1 * from t where id = @id) begin if @c = 1 begin update t set c1 = @cv end else begin if @c = 2 update t set c2 = @cv end end else insert into t (c1) values (@cv); end自己再根据实际内容写吧
1、if exists (select 1 from tb where 唯一列 = @参数) insert into tb... else update tb set ... where 唯一列 = @参数2、这种更新要用动态去执行,列名有什么规律么?按照传入的参数值去拼接更新的SQL语句。declare @str varchar(max) declare @i int set @str = 1 set @i = 1 set @str = (case when @i=1 then 'col1' when @i=2 then 'col2' else 'col3' end) set @str = ' update tb set '+@str+' = ... where ... ' exec(@str)
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执行结果却是正确,求助啊
begin
if @c = 1
begin
update t
set c1 = @cv
end
else
begin if @c = 2
update t
set c2 = @cv
end
end
else
insert into t (c1) values (@cv);
end自己再根据实际内容写吧
insert into tb...
else
update tb
set ...
where 唯一列 = @参数2、这种更新要用动态去执行,列名有什么规律么?按照传入的参数值去拼接更新的SQL语句。declare @str varchar(max)
declare @i int
set @str = 1
set @i = 1
set @str = (case when @i=1 then 'col1' when @i=2 then 'col2' else 'col3' end)
set @str = ' update tb set '+@str+' = ... where ... '
exec(@str)
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执行结果却是正确,求助啊