不才,找到了些简单的错误,不知道找对了吗:
CREATE PROCEDURE Evlsp_MoveData
@TbName varchar(200),
@ReturnValue int output
as
set @ReturnValue=0
declare @temp varchar(200)
declare @doccount int
declare @maxNumber int,@i int,@j int
if not exists(select Doc_Dbpath from Evl_FlowNumber where Doc_Dbpath=@Tbname)
begin
--声明并打开一个scroll游标
DECLARE moveCur Scroll cursor
for
select * from @tbname
open moveCur
--显示游标集合中数据行数
set @i=@@cursor_Rows
--读取游标中的延第一行数据
/****************************
declare @DocFlowNumber bigint
原来的:DECALRE @DocFlowNumber bigint
********************************/
DECLARE @Nsf varchar(100),@DocID char(32),@DocCategory varchar(200)
DECLARE @subject varchar(200),@Click smallint,@Reader varchar(20),@ReadDate datetime
DECLARE @CreateTime datetime,@Creator varchar(4000),@Parameter varchar(200)
for @j=1 to @i
begin
Fetch absolute @j FROM moveCur INTO @DocID,@TbName,@DocFlownumber,@DocCategory,@Subtemp,@temp,@creator,@subject,@Reader,@ReadDate
SELECT TOP 1 @maxNumber=Doc_FlowNumber from Evl_FlowNumber order by Doc_FlowNumber desc
if @maxNumber<>"" then
begin
insert into Evl_Flownumber (Doc_ID,Doc_Dbpath,Doc_FlowNumber,Doc_Category,Doc_Click,Doc_CreateTime,Doc_Creator,Doc_Subject,Doc_RecentReaders,Doc_ReadDate)
values(@DocID,@TbName,@DocFlownumber,@DocCategory,@Subtemp,@temp,@creator,@subject,@Reader,convert(varchar(18),@ReadDate)/****这里少了个括号****/
end
else
/********************************改了这里*****************
set @DocFlowNumber=datepart(yy,@createtime)+'000001'
原来的:@DocFlowNumber=datepart(yy,@createtime)+'000001'
*****×××××××*****改了这里*******************************/
insert into Evl_Flownumber (Doc_ID,Doc_Dbpath,Doc_FlowNumber,Doc_Category,Doc_Click,Doc_CreateTime,Doc_Creator,Doc_Subject,Doc_RecentReaders,Doc_ReadDate)
values(@DocID,@TbName,@DocFlownumber,@DocCategory,@Subtemp,@temp,@creator,@subject,@Reader,convert(varchar(18),@ReadDate)/****这里少了个括号****/
end
set @ReturnValue=1
GO
我可能没有说明白,我在B中是个空表,但是我要将A中的记录转移到B中,B表的设计相对于A多了两个字段,且其中的一个字段为编号,这个编号要依照一定的原则,所以不能用自增字段,有人说要涉及到游标,可我确实不懂,哪位能给出实际的例子?
CREATE PROCEDURE Evlsp_MoveData @TbName varchar(200),@ReturnValue int output
as
set @ReturnValue=0
declare @temp varchar(200)
declare @doccount int
declare @maxNumber int,@i int,@j int
if not exists(select Doc_Dbpath from Evl_FlowNumber where Doc_Dbpath=@Tbname)
begin
--声明并打开一个scroll游标
DECLARE moveCur Scroll cursor
for
select * from @tbname
open moveCur
--显示游标集合中数据行数
set @i=@@cursor_Rows
for @j=1 to @i
begin
Fetch absolute @j FROM moveCur INTO @DocID,@TbName,@DocFlownumber,@DocCategory,@Subtemp,@temp,@creator,@subject,@Reader,@ReadDate
SELECT TOP 1 @maxNumber=Doc_FlowNumber from Evl_FlowNumber order by Doc_FlowNumber desc
if @maxNumber<>"" then
begin
@DocFlowNumber=@maxNumber+1
insert into Evl_Flownumber (Doc_ID,Doc_Dbpath,Doc_FlowNumber,Doc_Category,Doc_Click,Doc_CreateTime,Doc_Creator,Doc_Subject,Doc_RecentReaders,Doc_ReadDate) values(@DocID,@TbName,@DocFlownumber,@DocCategory,@Subtemp,@temp,@creator,@subject,@Reader,convert(varchar(18),@ReadDate)
end
else
@DocFlowNumber=datepart(yy,@createtime)+'000001'
insert into Evl_Flownumber (Doc_ID,Doc_Dbpath,Doc_FlowNumber,Doc_Category,Doc_Click,Doc_CreateTime,Doc_Creator,Doc_Subject,Doc_RecentReaders,Doc_ReadDate) values(@DocID,@TbName,@DocFlownumber,@DocCategory,@Subtemp,@temp,@creator,@subject,@Reader,convert(varchar(18),@ReadDate)
end
set @ReturnValue=1
GO
我试写了一下,有很多错误,哪位能帮忙调试一下?