SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GOCREATE PROCEDURE [dbo].[Update_tab_goodsCcode]
(
@TcodeOld varchar(50)
)
AS
--declare @TcodeOld varchar(50)
--set @TcodeOld='0/1/14/'
declare @tmp int
select '0' as flag,nId,Ccode,Cname,CunitBig,Mprice,Wid into #a from TAB_goods
where Ccode = @TcodeOld --把要处理的记录放到#a中,并做了一个标记 flag --select * from #a
--drop table #adeclare @num int
set @num=100set @tmp = (select top 1 flag from #a where flag=0 order by nid)
while @tmp =0
begin --选择出第一个标记的------------------------------------------------
--处理过程 --处理set @num=@num+1
update #a set Ccode=@TcodeOld+cast(@num as nvarchar(10)) where nid
in
(select top 1 nid from #a where flag ='0' order by nid)
declare @NumID nvarchar(20)
set @NumID=(select top 1 nid from #a where flag ='0' order by nid)
--更改表的Ccode
update TAB_goods set Ccode=@TcodeOld+cast(@num as nvarchar(10))
where TAB_goods.nId=@NumID
--将数据插入TAB_inOut中declare @Cunit varchar(50)
declare @Mprice float
declare @Nhow int
select top 1 @Cunit=a.CunitBig,@Mprice=a.Mprice,@Nhow=a.Wid from #a a where flag ='0' order by nid
insert TAB_inOut(CgoodsCode,CoutDep,Nhow,Cunit,Mprice) --,Cno1,nAllNum
values(@TcodeOld+cast(@num as nvarchar(10)),1,@Nhow,@Cunit,@Mprice)
------------------------------------------------
update #a set flag ='1' where flag ='0' and nid=@NumID
--根据标记,处理你的标记
set @tmp = (select top 1 flag from #a where flag ='0'order by nid)
If @tmp is null
BREAK
Else
CONTINUE
end --select cast(@num-100 as int) as Hangdrop table #aGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS OFF
GOCREATE PROCEDURE [dbo].[Update_tab_goodsCcode]
(
@TcodeOld varchar(50)
)
AS
--declare @TcodeOld varchar(50)
--set @TcodeOld='0/1/14/'
declare @tmp int
select '0' as flag,nId,Ccode,Cname,CunitBig,Mprice,Wid into #a from TAB_goods
where Ccode = @TcodeOld --把要处理的记录放到#a中,并做了一个标记 flag --select * from #a
--drop table #adeclare @num int
set @num=100set @tmp = (select top 1 flag from #a where flag=0 order by nid)
while @tmp =0
begin --选择出第一个标记的------------------------------------------------
--处理过程 --处理set @num=@num+1
update #a set Ccode=@TcodeOld+cast(@num as nvarchar(10)) where nid
in
(select top 1 nid from #a where flag ='0' order by nid)
declare @NumID nvarchar(20)
set @NumID=(select top 1 nid from #a where flag ='0' order by nid)
--更改表的Ccode
update TAB_goods set Ccode=@TcodeOld+cast(@num as nvarchar(10))
where TAB_goods.nId=@NumID
--将数据插入TAB_inOut中declare @Cunit varchar(50)
declare @Mprice float
declare @Nhow int
select top 1 @Cunit=a.CunitBig,@Mprice=a.Mprice,@Nhow=a.Wid from #a a where flag ='0' order by nid
insert TAB_inOut(CgoodsCode,CoutDep,Nhow,Cunit,Mprice) --,Cno1,nAllNum
values(@TcodeOld+cast(@num as nvarchar(10)),1,@Nhow,@Cunit,@Mprice)
------------------------------------------------
update #a set flag ='1' where flag ='0' and nid=@NumID
--根据标记,处理你的标记
set @tmp = (select top 1 flag from #a where flag ='0'order by nid)
If @tmp is null
BREAK
Else
CONTINUE
end --select cast(@num-100 as int) as Hangdrop table #aGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
解决方案 »
- 分布式查询几台电脑上的数据库
- 触发器的update如何只在某个字段的值发生改变时才执行?
- 帮我看看这条SQL语句是什么意思?内容看正文
- 使用Log Explorer?诸位大哥拜托了!!!
- //////////一个用于插入新记录的SP,错在哪呢?///////////////////
- 这题求答案!!!
- 无法访问Mysql!!!安装了WAMP5,但是无法访问。
- SQL-SERVER2000的sa密码藏在哪个文件里?
- 我该怎么取出sqlite3所有数据???????????/
- PB可以同时连接2个数据库吗,比方说一个是SQL SERVER ,一个是FOXPRO的
- 这个问题如何解决
- 请问怎么统计列的值?
2,选择带标记的第一个,获得对应的ID
3,根据ID,处理你要处理的过程
4,更新ID对应的标记,一半循环去到下一个。
set Value=$value
Where ID in (Select Top 1 ID From Table Where ID=$Value Order By ID ***(代表升序))
1。将原记录满足条件并排序放到临时表。
select *,0 as flag into # from tablename where ....
2.对临时表进行更新,变量来判断是否更新。
decalre @a1 --上行记录的条件值
declare @a2 --本行记录的条件值
declare @flag --被更新行的标志
update #
set @a2=col,
@flag=case @a1=@a2 then 0 else 1 end,
更新列=case @flag when 1 then 值 else 更新列 end,
flag=@flag,
@a1=col
3.获取被更新的结果。
select * from # where flag=1
set rowcount 0