CREATE PROCEDURE [dbo].[AutoImportToDelby] @@Csno nvarchar(15), @@delno nvarchar(15), @@port varchar(50) AS ---不考虑产出量 declare @table_delby table(serno [int] IDENTITY (1, 1) NOT NULL,delno nvarchar(15),scno nvarchar(15), pdno nvarchar(15),pddesc varchar(25),tot_qty int,qty_prod int,qty_del int,diff_qty int,sales varchar(10),prc float)declare @scno nvarchar(15),@qty int,@qty_prod int,@qty_del int,@Pdno nvarchar(15),@Pddesc varchar(25),@sales varchar(10),@prc floatdeclare mydelby cursor for --select H.SCNO,H.Qty,H.Qty_prod,H.Qty_del,H.Pdno,H.Pddesc from (select * from (select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A.Pdno,A.Pddesc,B.csno from scby A left join (select * from sc where csno=@@Csno) B on A.scno=b.scno)D where qty-qty_del>0)H Left join del J on H.csno=J.csno select SCNO,Qty,Qty_prod,Qty_del,Pdno,Pddesc,sales,prc from(select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A .Pdno,A.Pddesc,B.sales,c.delno,A.prc from scby A join (select * from sc where csno=@@Csno and port=@@port) B on A.scno=b.scno join del C on B.csno=c.csno)D where qty-qty_del>0 and delno=@@delno open mydelbyfetch mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc while @@Fetch_Status=0 begin insert into @table_delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc) values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del,@sales,@prc) --insert into delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty)values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del) fetch mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc end close mydelby deallocate mydelby insert into delby(serno,delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc)select * from @table_delby GO
CREATE PROCEDURE [dbo].[AutoImportToDelby] @Csno nvarchar(15), @delno nvarchar(15), @port varchar(50) AS ---不考虑产出量 declare @table_delby table(serno [int] IDENTITY (1, 1) NOT NULL,delno nvarchar(15),scno nvarchar(15), pdno nvarchar(15),pddesc varchar(25),tot_qty int,qty_prod int,qty_del int,diff_qty int,sales varchar(10),prc float) declare @scno nvarchar(15),@qty int,@qty_prod int,@qty_del int,@Pdno nvarchar(15),@Pddesc varchar(25),@sales varchar(10),@prc float declare mydelby cursor for --select H.SCNO,H.Qty,H.Qty_prod,H.Qty_del,H.Pdno,H.Pddesc from (select * from (select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A.Pdno,A.Pddesc,B.csno from scby A left join (select * from sc where csno=@Csno) B on A.scno=b.scno)D where qty-qty_del>0)H Left join del J on H.csno=J.csno select SCNO,Qty,Qty_prod,Qty_del,Pdno,Pddesc,sales,prc from(select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A .Pdno,A.Pddesc,B.sales,c.delno,A.prc from scby A join (select * from sc where csno=@Csno and port=@port) B on A.scno=b.scno join del C on B.csno=c.csno)D where qty-qty_del>0 and delno=@delno open mydelby fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc while @@Fetch_Status=0 begin insert into @table_delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc) values(@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del,@sales,@prc) --insert into delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty)values(@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del) fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc end close mydelby deallocate mydelby insert into delby(serno,delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc)select * from @table_delby GO--这样呢
你原来的 fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc都改为 fetch mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc我贴的语句我这里没有提示出错,你还有其他没贴出的语句吗?
另@@Csno@@在sql2000里没有用的你要改为@
CREATE PROCEDURE [dbo].[AutoImportToDelby] @@Csno nvarchar(15), @@delno nvarchar(15), @@port varchar(50) AS ---不考虑产出量 declare @table_delby table(serno [int] IDENTITY (1, 1) NOT NULL,delno nvarchar(15),scno nvarchar(15), pdno nvarchar(15),pddesc varchar(25),tot_qty int,qty_prod int,qty_del int,diff_qty int,sales varchar(10),prc float) declare @scno nvarchar(15),@qty int,@qty_prod int,@qty_del int,@Pdno nvarchar(15),@Pddesc varchar(25),@sales varchar(10),@prc float declare mydelby cursor for --select H.SCNO,H.Qty,H.Qty_prod,H.Qty_del,H.Pdno,H.Pddesc from (select * from (select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A.Pdno,A.Pddesc,B.csno from scby A left join (select * from sc where csno=@@Csno) B on A.scno=b.scno)D where qty-qty_del>0)H Left join del J on H.csno=J.csno select SCNO,Qty,Qty_prod,Qty_del,Pdno,Pddesc,sales,prc from(select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A .Pdno,A.Pddesc,B.sales,c.delno,A.prc from scby A join (select * from sc where csno=@@Csno and port=@@port) B on A.scno=b.scno join del C on B.csno=c.csno)D where qty-qty_del>0 and delno=@@delno open mydelby fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc while @@Fetch_Status=0 begin insert into @table_delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc) values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del,@sales,@prc) --insert into delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty)values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del) fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc end close mydelby deallocate mydelby insert into delby(serno,delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc)select * from @table_delby GO 以上是存储过程; 以下是C#写的调用过程的代码: Variable.App_Conn = DBService.OpenConnect(); ///Define SQL COmmand With Transaction SqlCommand sqlCmd = new SqlCommand("AutoImportToDelby", Variable.App_Conn); sqlCmd.CommandType = CommandType.StoredProcedure; SqlParameter param; param = sqlCmd.Parameters.Add("@@Csno", SqlDbType.NVarChar, 15); param = sqlCmd.Parameters.Add("@@delno", SqlDbType.NVarChar, 15); param = sqlCmd.Parameters.Add("@@port", SqlDbType.VarChar, 50); sqlCmd.Parameters["@@Csno"].Value = txt_CSNOS00.Text; sqlCmd.Parameters["@@delno"].Value = txt_DELNOS01.Text; sqlCmd.Parameters["@@port"].Value = txt_DEL_PORTS00.Text; sqlCmd.ExecuteNonQuery(); DBService.CloseConnect(Variable.App_Conn); 执行调用时抛出异常:“名为'next'游标不存在,语句已终止”(注:以前执行调用是没问题的,但今天就出现了这个怪异的现象)
我调了一下,如果把 while @@Fetch_Status=0 begin insert into @table_delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc) values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del,@sales,@prc) --insert into delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty)values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del) fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc end 中只保留fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc这条语句则可通过 但我的其他存储过程也是这样写的为什么就可以呢,再有这个存储过程在以前是可以通过的!
@@Csno nvarchar(15),
@@delno nvarchar(15),
@@port varchar(50)
AS
---不考虑产出量
declare @table_delby table(serno [int] IDENTITY (1, 1) NOT NULL,delno nvarchar(15),scno nvarchar(15),
pdno nvarchar(15),pddesc varchar(25),tot_qty int,qty_prod int,qty_del int,diff_qty int,sales varchar(10),prc float)declare @scno nvarchar(15),@qty int,@qty_prod int,@qty_del int,@Pdno nvarchar(15),@Pddesc varchar(25),@sales varchar(10),@prc floatdeclare mydelby cursor for
--select H.SCNO,H.Qty,H.Qty_prod,H.Qty_del,H.Pdno,H.Pddesc from (select * from (select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A.Pdno,A.Pddesc,B.csno from scby A left join (select * from sc where csno=@@Csno) B on A.scno=b.scno)D where qty-qty_del>0)H Left join del J on H.csno=J.csno
select SCNO,Qty,Qty_prod,Qty_del,Pdno,Pddesc,sales,prc from(select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A
.Pdno,A.Pddesc,B.sales,c.delno,A.prc from scby A join (select * from sc where csno=@@Csno and port=@@port) B on A.scno=b.scno
join del C on B.csno=c.csno)D where qty-qty_del>0 and delno=@@delno
open mydelbyfetch mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc
while @@Fetch_Status=0
begin
insert into @table_delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc)
values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del,@sales,@prc)
--insert into delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty)values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del)
fetch mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc
end
close mydelby
deallocate mydelby
insert into delby(serno,delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc)select * from @table_delby
GO
钻石兄弟,我按你的改动执行依然报如下错误:“名为'next'游标不存在,语句已终止”
@Csno nvarchar(15),
@delno nvarchar(15),
@port varchar(50)
AS
---不考虑产出量
declare @table_delby table(serno [int] IDENTITY (1, 1) NOT NULL,delno nvarchar(15),scno nvarchar(15),
pdno nvarchar(15),pddesc varchar(25),tot_qty int,qty_prod int,qty_del int,diff_qty int,sales varchar(10),prc float)
declare @scno nvarchar(15),@qty int,@qty_prod int,@qty_del int,@Pdno nvarchar(15),@Pddesc varchar(25),@sales varchar(10),@prc float
declare mydelby cursor for
--select H.SCNO,H.Qty,H.Qty_prod,H.Qty_del,H.Pdno,H.Pddesc from (select * from (select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A.Pdno,A.Pddesc,B.csno from scby A left join (select * from sc where csno=@Csno) B on A.scno=b.scno)D where qty-qty_del>0)H Left join del J on H.csno=J.csno
select SCNO,Qty,Qty_prod,Qty_del,Pdno,Pddesc,sales,prc from(select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A
.Pdno,A.Pddesc,B.sales,c.delno,A.prc from scby A join (select * from sc where csno=@Csno and port=@port) B on A.scno=b.scno
join del C on B.csno=c.csno)D where qty-qty_del>0 and delno=@delno open mydelby
fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc
while @@Fetch_Status=0
begin
insert into @table_delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc)
values(@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del,@sales,@prc)
--insert into delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty)values(@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del)
fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc
end
close mydelby
deallocate mydelby
insert into delby(serno,delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc)select * from @table_delby
GO--这样呢
fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc都改为
fetch mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc我贴的语句我这里没有提示出错,你还有其他没贴出的语句吗?
@@Csno nvarchar(15),
@@delno nvarchar(15),
@@port varchar(50)
AS
---不考虑产出量
declare @table_delby table(serno [int] IDENTITY (1, 1) NOT NULL,delno nvarchar(15),scno nvarchar(15),
pdno nvarchar(15),pddesc varchar(25),tot_qty int,qty_prod int,qty_del int,diff_qty int,sales varchar(10),prc float)
declare @scno nvarchar(15),@qty int,@qty_prod int,@qty_del int,@Pdno nvarchar(15),@Pddesc varchar(25),@sales varchar(10),@prc float
declare mydelby cursor for
--select H.SCNO,H.Qty,H.Qty_prod,H.Qty_del,H.Pdno,H.Pddesc from (select * from (select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A.Pdno,A.Pddesc,B.csno from scby A left join (select * from sc where csno=@@Csno) B on A.scno=b.scno)D where qty-qty_del>0)H Left join del J on H.csno=J.csno
select SCNO,Qty,Qty_prod,Qty_del,Pdno,Pddesc,sales,prc from(select A.SCNO,A.Qty,A.Qty_prod,A.Qty_del,A
.Pdno,A.Pddesc,B.sales,c.delno,A.prc from scby A join (select * from sc where csno=@@Csno and port=@@port) B on A.scno=b.scno
join del C on B.csno=c.csno)D where qty-qty_del>0 and delno=@@delno
open mydelby
fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc
while @@Fetch_Status=0
begin
insert into @table_delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc)
values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del,@sales,@prc)
--insert into delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty)values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del)
fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc
end
close mydelby
deallocate mydelby
insert into delby(serno,delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc)select * from @table_delby
GO
以上是存储过程;
以下是C#写的调用过程的代码:
Variable.App_Conn = DBService.OpenConnect();
///Define SQL COmmand With Transaction
SqlCommand sqlCmd = new SqlCommand("AutoImportToDelby", Variable.App_Conn);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = sqlCmd.Parameters.Add("@@Csno", SqlDbType.NVarChar, 15);
param = sqlCmd.Parameters.Add("@@delno", SqlDbType.NVarChar, 15);
param = sqlCmd.Parameters.Add("@@port", SqlDbType.VarChar, 50);
sqlCmd.Parameters["@@Csno"].Value = txt_CSNOS00.Text;
sqlCmd.Parameters["@@delno"].Value = txt_DELNOS01.Text;
sqlCmd.Parameters["@@port"].Value = txt_DEL_PORTS00.Text;
sqlCmd.ExecuteNonQuery();
DBService.CloseConnect(Variable.App_Conn);
执行调用时抛出异常:“名为'next'游标不存在,语句已终止”(注:以前执行调用是没问题的,但今天就出现了这个怪异的现象)
while @@Fetch_Status=0
begin
insert into @table_delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty,sales,prc)
values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del,@sales,@prc)
--insert into delby(delno,SCNO,PDNO,Pddesc,Tot_qty,Qty_prod,QTY_DEL,diff_qty)values(@@delno,@scno,@Pdno,@Pddesc,@qty,@qty_prod,@qty_del,@qty-@qty_del)
fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc
end
中只保留fetch next from mydelby into @scno,@qty,@qty_prod,@qty_del,@Pdno,@Pddesc,@sales,@prc这条语句则可通过
但我的其他存储过程也是这样写的为什么就可以呢,再有这个存储过程在以前是可以通过的!