Declare myCursor Cursor For
Select * From DailyProduceDetail
-->
Declare myCursor Cursor For
Select * From DailyProduceDetail FOR UPDATE 不过你可以不必用游标实现的:CREATE PROCEDURE pro_DailyOrder
AS
Select *,IDENTITY(int,1,1) as id0
into #temp
From DailyProduceDetailexec ('truncate table DailyProduceDetail')insert DailyProduceDetail(DailyOrder,col1,col2,...) --列出除DailyOrder外的所有字段
select id0,col1,col2,...
from #Tempdrop table #Temp
GO
Select * From DailyProduceDetail
-->
Declare myCursor Cursor For
Select * From DailyProduceDetail FOR UPDATE 不过你可以不必用游标实现的:CREATE PROCEDURE pro_DailyOrder
AS
Select *,IDENTITY(int,1,1) as id0
into #temp
From DailyProduceDetailexec ('truncate table DailyProduceDetail')insert DailyProduceDetail(DailyOrder,col1,col2,...) --列出除DailyOrder外的所有字段
select id0,col1,col2,...
from #Tempdrop table #Temp
GO
AS
Select *,IDENTITY(int,1,1) as id0
into #temp
From DailyProduceDetail
order by DailyProNumexec ('truncate table DailyProduceDetail')insert DailyProduceDetail(DailyOrder,DailyProNum)
select id0,DailyProNum
from #Tempdrop table #Temp
GO
首先對您的幫助表示感謝!
而且您的第二種方法(建立臨時表)也完全可以,而且速度比用Cursor來的快.非常感謝!
但我還有幾個小問題向您請教:
1.
如果我在申明Cursor時用了Order By.如:
Declare myCursor Cursor For
Select * From DailyProduceDetail order by DailyProNum For Update
那麼Cursor是不是就是只讀的,不能對它進行Update??
2.您知道那裡有比較詳細的Sql命令的文檔下載嗎?
2.你可以定义参数
CREATE PROCEDURE pro_DailyOrder
AS
Declare @InitOrder Int,
@DailyOrder int,
@DailyProNum int
Set @InitOrder=1
Declare myCursor Cursor For
Select * From DailyProduceDetail order by dailypronum
Open myCursor
Fetch myCursor into @DailyOrder, @DailyProNum
While @@FETCH_STATUS=0
Begin
update DailyProduceDetail Set @DailyOrder=@InitOrder
set @InitOrder=@InitOrder+1
FETCH Next From myCursor into @DailyOrder, @DailyProNum
End
Close myCursor
DEALLOCATE myCursor
GO
首先多謝你.
但你的update DailyProduceDetail Set @DailyOrder=@InitOrder明顯達不到變更DailyOrder的要求.