有存储过程:UpdateName 用来修改用户名。 参数为: NewName,PID 分别表示 新的用户名,用户ID现在有表New。字段为:PID 、NewName 。 分别有用户ID以及修改后对应的用户名。 现在想用游标来实现。该如何写?谢谢
比如
原数据为:1 jj
2 hh
3 kk表New: 1 uu
2 ii
3 oo
用UpdateName这个存储过程做了用户名修改后,原数据变为:
1 uu
2 ii
3 oo
比如
原数据为:1 jj
2 hh
3 kk表New: 1 uu
2 ii
3 oo
用UpdateName这个存储过程做了用户名修改后,原数据变为:
1 uu
2 ii
3 oo
不能用update
select 1,'jj'
union all
select 2,'hh'
union all
select 3,'kk'insert into new
select 1,'uu'
union all
select 2,'ii'
union all
select 3,'oo'create procedure pr_ChangeUserName
as
begin
declare @newID int
declare @newName nvarchar(20)declare cur cursor
for select pid,newName from newopen cur
fetch next from cur into @newID,@newName
while @@fetch_status =0
begin
update old set [name] = @newName where pid = @newID
fetch next from cur into @newID,@newName
endclose cur
deallocate cur
endselect * from oldpid name
----------- --------------------
1 uu
2 ii
3 oo(3 row(s) affected)
------请看清楚,我是要用存储过程来修改哈
这是用存储过程修改的啊,,存储过程里也要用update语句的啊,
create table old(pid int,[name] nvarchar(20))create table new(pid int,[newName] nvarchar(20))insert into old
select 1,'jj'
union all
select 2,'hh'
union all
select 3,'kk'insert into new
select 1,'uu'
union all
select 2,'ii'
union all
select 3,'oo'create procedure cc(@NewName nvarchar(20),@PID int )
as
begin
update old set [name] = @NewName where pid = @PID
end
create procedure pr_ChangeUserName
as
begin
declare @newID int
declare @newName nvarchar(20)declare cur cursor
for select pid,newName from newopen cur
fetch next from cur into @newID,@newName
while @@fetch_status =0
begin
exec cc @newName,@newID
fetch next from cur into @newID,@newName
endclose cur
deallocate cur
endexec pr_ChangeUserName
select * from oldpid name
----------- --------------------
1 uu
2 ii
3 oo(3 row(s) affected)
declare c1 cursor for
select pid,newname from [New]
open c1
fetch next from c1 into @pid,@newname
while @@fetch_status=0
begin
Exec dbo.UpdateName @pid,@newname
fetch next from c1 into @pid,@newname
end
close c1
deallocate c1