为什么我的存储过程在查询分析器下可以运行,为什么通过DELPHI的ADOQUERY和ADOStoredProc要出现EOLEEXCEPTION的错误,报错后存储过程还是顺利的完成了,为什么???
内容如下:CREATE PROCEDURE dbo.CXZJ
@CXXMID Int,
@TJ Int,
@DQ Int,
@GD Int,
@ZS Int,
@JS Int,
@ZJ Int,
@DJ Int,
@JL Int, @Condition Nvarchar(1000)
AS
Declare @CXZY NChar(10),@CXRS Int,@ZJRS Int,@SQLString NVarChar(4000),
@RS NChar(8),@i Int,@CXNumber Int,@ConditionEX NVarChar(1000)
Select @CXNumber=Max(CXNumber)+1 from TabCXZJNR
Set @i=1
While @i<=8
begin
if (@i=1)
begin
Set @CXRS=@TJ Set @ConditionEX=@Condition+N' And ZY_TJ=1'
end if (@i=2)
begin
Set @CXRS=@DQ Set @ConditionEX=@Condition+N' And ZY_DQ=1'
end
if (@i=3)
begin
Set @CXRS=@GD Set @ConditionEX=@Condition+N' And ZY_GD=1'
end
if (@i=4)
begin
Set @CXRS=@ZS Set @ConditionEX=@Condition+N' And ZY_ZS=1'
end
if (@i=5)
begin
Set @CXRS=@JS Set @ConditionEX=@Condition+N' And ZY_JS=1'
end if (@i=6)
begin
Set @CXRS=@ZJ Set @ConditionEX=@Condition+N' And ZY_ZJ=1'
end if (@i=7)
begin
Set @CXRS=@DJ Set @ConditionEX=@Condition+N' And ZY_DJ=1'
end
if (@i=8)
begin
Set @CXRS=@JL Set @ConditionEX=@Condition+N' And ZY_JL=1'
end
if (@CXRS<>0)
begin
Set @SQLString=N'
Declare
@id Int,@XM VarChar(8),@XB Char(2),@BH VarChar(30),
@SFZ VarChar(18),@DW VarChar(50),@ZW VarChar(16),
@ZC VarChar(16),@ZY VarChar(20),@XL Char(14),
@DH VarChar(8),@SJ VarChar(12),@CH VarChar(12)
Declare
@CXXMID Int,@CutNum Int,@CXRS Int,@i Int,@NextNum Int,
@CXNumber Int,@RANDNum float
Set @CXXMID='+Cast(@CXXMID as NChar(8))+
N'Set @CXNumber='+Cast(@CXNumber as NChar(8))+
N'Set @CXRS='+Cast(@CXRS as NChar(8))+
N'Declare CXZJ_Cursor CURSOR
LOCAL SCROLL STATIC
FOR
Select id,XM,XB,ZSBH,SFZ,DW,ZW,ZC,ZY,XL,DH,SJ,CH
from TabZJ_NJ as aa
Where (not exists (Select * from TabCXZJNR,TabZJ_NJ
Where (TabCXZJNR.id=@CXXMID and TabCXZJNR.ZJID=aa.id)))'+
@ConditionEX+
N'Open CXZJ_Cursor
Set @CutNum=@@CURSOR_ROWS/@CXRS
if (@CutNum=0)
begin
Fetch First From CXZJ_Cursor
Into
@id,@XM,@XB,@BH,@SFZ,@DW,@ZW,@ZC,@ZY,@XL,@DH,@SJ,@CH
while @@Fetch_Status=0
begin
Insert TabCXZJNR (id,ZJID,XM,XB,BH,SFZ,DW,ZW,ZC,ZY,XL,DH,SJ,CH,CXNumber)
Values (@CXXMID,@id,@XM,@XB,@BH,@SFZ,@DW,@ZW,@ZC,@ZY,@XL,@DH,@SJ,@CH,@CXNumber)
Fetch Next From CXZJ_Cursor
Into
@id,@XM,@XB,@BH,@SFZ,@DW,@ZW,@ZC,@ZY,@XL,@DH,@SJ,@CH
end
end else begin
Set @i=0
while @i<@CXRS
begin
Set @NextNum=CEILiNG(@CutNum*(@i+Rand(checksum(newid()))))
Fetch FIRST From CXZJ_Cursor
Fetch ABSOLUTE @NextNum From CXZJ_Cursor
Into
@id,@XM,@XB,@BH,@SFZ,@DW,@ZW,@ZC,@ZY,@XL,@DH,@SJ,@CH
Insert TabCXZJNR (id,ZJID,XM,XB,BH,SFZ,DW,ZW,ZC,ZY,XL,DH,SJ,CH,CXNumber)
Values (@CXXMID,@id,@XM,@XB,@BH,@SFZ,@DW,@ZW,@ZC,@ZY,@XL,@DH,@SJ,@CH,@CXNumber)
Set @i=@i+1
end
ClOSE CXZJ_Cursor
DEALLOCATE CXZJ_Cursor
end
'
EXEC sp_executesql @SQLString
end
Set @i=@i+1
end;
Select @CXNumber as 'CXNumber'
GO
内容如下:CREATE PROCEDURE dbo.CXZJ
@CXXMID Int,
@TJ Int,
@DQ Int,
@GD Int,
@ZS Int,
@JS Int,
@ZJ Int,
@DJ Int,
@JL Int, @Condition Nvarchar(1000)
AS
Declare @CXZY NChar(10),@CXRS Int,@ZJRS Int,@SQLString NVarChar(4000),
@RS NChar(8),@i Int,@CXNumber Int,@ConditionEX NVarChar(1000)
Select @CXNumber=Max(CXNumber)+1 from TabCXZJNR
Set @i=1
While @i<=8
begin
if (@i=1)
begin
Set @CXRS=@TJ Set @ConditionEX=@Condition+N' And ZY_TJ=1'
end if (@i=2)
begin
Set @CXRS=@DQ Set @ConditionEX=@Condition+N' And ZY_DQ=1'
end
if (@i=3)
begin
Set @CXRS=@GD Set @ConditionEX=@Condition+N' And ZY_GD=1'
end
if (@i=4)
begin
Set @CXRS=@ZS Set @ConditionEX=@Condition+N' And ZY_ZS=1'
end
if (@i=5)
begin
Set @CXRS=@JS Set @ConditionEX=@Condition+N' And ZY_JS=1'
end if (@i=6)
begin
Set @CXRS=@ZJ Set @ConditionEX=@Condition+N' And ZY_ZJ=1'
end if (@i=7)
begin
Set @CXRS=@DJ Set @ConditionEX=@Condition+N' And ZY_DJ=1'
end
if (@i=8)
begin
Set @CXRS=@JL Set @ConditionEX=@Condition+N' And ZY_JL=1'
end
if (@CXRS<>0)
begin
Set @SQLString=N'
Declare
@id Int,@XM VarChar(8),@XB Char(2),@BH VarChar(30),
@SFZ VarChar(18),@DW VarChar(50),@ZW VarChar(16),
@ZC VarChar(16),@ZY VarChar(20),@XL Char(14),
@DH VarChar(8),@SJ VarChar(12),@CH VarChar(12)
Declare
@CXXMID Int,@CutNum Int,@CXRS Int,@i Int,@NextNum Int,
@CXNumber Int,@RANDNum float
Set @CXXMID='+Cast(@CXXMID as NChar(8))+
N'Set @CXNumber='+Cast(@CXNumber as NChar(8))+
N'Set @CXRS='+Cast(@CXRS as NChar(8))+
N'Declare CXZJ_Cursor CURSOR
LOCAL SCROLL STATIC
FOR
Select id,XM,XB,ZSBH,SFZ,DW,ZW,ZC,ZY,XL,DH,SJ,CH
from TabZJ_NJ as aa
Where (not exists (Select * from TabCXZJNR,TabZJ_NJ
Where (TabCXZJNR.id=@CXXMID and TabCXZJNR.ZJID=aa.id)))'+
@ConditionEX+
N'Open CXZJ_Cursor
Set @CutNum=@@CURSOR_ROWS/@CXRS
if (@CutNum=0)
begin
Fetch First From CXZJ_Cursor
Into
@id,@XM,@XB,@BH,@SFZ,@DW,@ZW,@ZC,@ZY,@XL,@DH,@SJ,@CH
while @@Fetch_Status=0
begin
Insert TabCXZJNR (id,ZJID,XM,XB,BH,SFZ,DW,ZW,ZC,ZY,XL,DH,SJ,CH,CXNumber)
Values (@CXXMID,@id,@XM,@XB,@BH,@SFZ,@DW,@ZW,@ZC,@ZY,@XL,@DH,@SJ,@CH,@CXNumber)
Fetch Next From CXZJ_Cursor
Into
@id,@XM,@XB,@BH,@SFZ,@DW,@ZW,@ZC,@ZY,@XL,@DH,@SJ,@CH
end
end else begin
Set @i=0
while @i<@CXRS
begin
Set @NextNum=CEILiNG(@CutNum*(@i+Rand(checksum(newid()))))
Fetch FIRST From CXZJ_Cursor
Fetch ABSOLUTE @NextNum From CXZJ_Cursor
Into
@id,@XM,@XB,@BH,@SFZ,@DW,@ZW,@ZC,@ZY,@XL,@DH,@SJ,@CH
Insert TabCXZJNR (id,ZJID,XM,XB,BH,SFZ,DW,ZW,ZC,ZY,XL,DH,SJ,CH,CXNumber)
Values (@CXXMID,@id,@XM,@XB,@BH,@SFZ,@DW,@ZW,@ZC,@ZY,@XL,@DH,@SJ,@CH,@CXNumber)
Set @i=@i+1
end
ClOSE CXZJ_Cursor
DEALLOCATE CXZJ_Cursor
end
'
EXEC sp_executesql @SQLString
end
Set @i=@i+1
end;
Select @CXNumber as 'CXNumber'
GO
还是把你的调用帖出来看看。
ADOStoredProc1.Prepared:=true; ADOStoredProc1.Parameters.CreateParameter('@CXXMID',ftInteger,pdInput,4,1);
ADOStoredProc1.Parameters.CreateParameter('@TJ',ftInteger,pdInput,4,1);
ADOStoredProc1.Parameters.CreateParameter('@DQ',ftInteger,pdInput,4,1);
ADOStoredProc1.Parameters.CreateParameter('@GD',ftInteger,pdInput,4,1);
ADOStoredProc1.Parameters.CreateParameter('@ZS',ftInteger,pdInput,4,1);
ADOStoredProc1.Parameters.CreateParameter('@JS',ftInteger,pdInput,4,1);
ADOStoredProc1.Parameters.CreateParameter('@ZJ',ftInteger,pdInput,4,1);
ADOStoredProc1.Parameters.CreateParameter('@DJ',ftInteger,pdInput,4,1);
ADOStoredProc1.Parameters.CreateParameter('@JL',ftInteger,pdInput,4,1); ADOStoredProc1.Parameters.CreateParameter('@Condition',ftWideString,pdInput,4000,'');
ADOStoredProc1.ExecProc;
ADOStoredProc1.Prepared:=true;
放在
ADOStoredProc1.Parameters.CreateParameter(……)之后ADOStoredProc1.ExecProc之前试试