要是在查询里怎么写呢?我有个查询select name,address from users现在希望用GenID为每个查询的结果赋一个不同的ID,得到像下面这样的数据U001,ZHANG,CQ U002,WANG,CQ U003,LI,CDU001,U002,U003是存储过程生成的
--例子create table #a(id int,aaa int,name varchar(20),cQ varchar(10)) insert into #a values(1,100,'ZHANG','CQ') insert into #a values(2,101,'WANG','CQ') insert into #a values(3,102,'LI','Cd') select * from #a id aaa name cq 1 100 ZHANG CQ 2 101 WANG CQ 3 102 LI Cd --现在更新aaa字段declare @id varchar(20) declare @newid int set @newid=200 DECLARE C_Cursor CURSOR FOR SELECT id FROM #a OPEN C_Cursor FETCH NEXT FROM C_Cursor into @id WHILE @@FETCH_STATUS = 0 begin
update #a set aaa=@newid where ID=@id set @newid=@newid+1
FETCH NEXT FROM C_Cursor into @id end CLOSE C_Cursor DEALLOCATE C_Cursorselect * from #a --结果 -------------------------------------- id aaa name cq 1 200 ZHANG CQ 2 201 WANG CQ 3 202 LI Cd -------------------------------------------------- --你需修改declare @id varchar(20) DECLARE C_Cursor CURSOR FOR SELECT id FROM #a OPEN C_Cursor FETCH NEXT FROM C_Cursor into @id WHILE @@FETCH_STATUS = 0 begin declare @newid int exec GenID @newid output update #a set aaa=@newid where ID=@id
FETCH NEXT FROM C_Cursor into @id end CLOSE C_Cursor DEALLOCATE C_Cursorselect * from #a -----------------------------------------
declare @id int
exec GenID @id output
update A set .... where ID=@id
应用于向表中插入新记录时进行ID赋值
declare @id char(10)
exec GenID @id output
insert into [A] ([ID]) values (@id)
U002,WANG,CQ
U003,LI,CDU001,U002,U003是存储过程生成的
insert into #a values(1,100,'ZHANG','CQ')
insert into #a values(2,101,'WANG','CQ')
insert into #a values(3,102,'LI','Cd')
select * from #a
id aaa name cq
1 100 ZHANG CQ
2 101 WANG CQ
3 102 LI Cd
--现在更新aaa字段declare @id varchar(20)
declare @newid int
set @newid=200
DECLARE C_Cursor CURSOR FOR
SELECT id FROM #a
OPEN C_Cursor
FETCH NEXT FROM C_Cursor into @id
WHILE @@FETCH_STATUS = 0
begin
update #a set aaa=@newid where ID=@id
set @newid=@newid+1
FETCH NEXT FROM C_Cursor into @id
end
CLOSE C_Cursor
DEALLOCATE C_Cursorselect * from #a
--结果
--------------------------------------
id aaa name cq
1 200 ZHANG CQ
2 201 WANG CQ
3 202 LI Cd
--------------------------------------------------
--你需修改declare @id varchar(20)
DECLARE C_Cursor CURSOR FOR
SELECT id FROM #a
OPEN C_Cursor
FETCH NEXT FROM C_Cursor into @id
WHILE @@FETCH_STATUS = 0
begin declare @newid int
exec GenID @newid output
update #a set aaa=@newid where ID=@id
FETCH NEXT FROM C_Cursor into @id
end
CLOSE C_Cursor
DEALLOCATE C_Cursorselect * from #a
-----------------------------------------