表A中 有字段 A1,A2,A3,A4,A5有两个参数 c和d
在表A中查寻如果在A2,A3,A4,A5中某一字段的值等于d就把等于d字段的前一个字段的值该为c
即 如过A5 = D 就把A4该为c
如过A4 = D 就把A3该为c
在表A中查寻如果在A2,A3,A4,A5中某一字段的值等于d就把等于d字段的前一个字段的值该为c
即 如过A5 = D 就把A4该为c
如过A4 = D 就把A3该为c
调试欢乐多
@c varchar(10)
@d varchar(10)
as
declare @i int
declare @s varchar(2000)
set @i=2
while @i<=5
begin
set @s='update tb set a'+cast(@i-1 as varchar)+'='''+@c+''' where a'+cast(@i as varchar)+'='''+@d +''''
exec(@s)
set @i=@i+1
end
@c varchar(10)
@d varchar(10)
as
declare @i int
declare @s varchar(2000)
set @i=2
while @i<=5
begin
set @s='update tb set a'+cast(@i-1 as varchar)+'='''+@c+''' where a'+cast(@i as varchar)+'='''+@d +''''
exec(@s)
set @i=@i+1
end
INSERT A(A1,A2,A3,A4,A5) SELECT 1,2,3,4,5
INSERT A(A1,A2,A3,A4,A5) SELECT 1,2,3,4,8
INSERT A(A1,A2,A3,A4,A5) SELECT 1,2,3,8,5SELECT * FROM A
GOCREATE PROC PU_A
@C INT,
@D INT
AS
BEGINUPDATE A SET A1=CASE WHEN A2=@D THEN @C ELSE A1 END,
A2=CASE WHEN A3=@D THEN @C ELSE A2 END,
A3=CASE WHEN A4=@D THEN @C ELSE A3 END,
A4=CASE WHEN A5=@D THEN @C ELSE A4 ENDEND
GOEXEC PU_A NULL,8SELECT * FROM ADROP PROC PU_A
DROP TABLE A
SET A1=@C WHERE A2=@D
GO
UPDATE A
SET A2=@C WHERE A3=@D
GO
UPDATE A
SET A3=@C WHERE A4=@D
GO
UPDATE A
SET A4=@C WHERE A5=@D
GO