同时返回记录集，传出output参数的存储过程,delphi调用它总返回空集(6分)

月满C楼 · 2008-06-27

Create Procedure Sp_PlateSend(   @outdate1 varchar(200),   @outdate2 varchar(200),   @OrderNo varchar(200),   @ProdNo varchar(200),   @outBaseNo varchar(200),   @state varchar(200),   @provider varchar(200),   @o_orderNum int output,   @o_total_weight float output,   @o_record_count int output ) as begin   declare     @sql varchar(4000),@condition varchar(4000)   set @sql='select       b.OutBaseNo as 电镀单号,       d.ProvidName as 计划电镀厂商,       d.ProvidName as 实发电镀厂商,       c.ShipDate as 发货日期,       c.PlateStart as 计划电镀日期,       b.OrderNo as 订单号,       b.ProdNo as 产品编码,       e.SerialName as 电镀色,       b.ProdName as 电镀名称,       b.Bag as 包数,       IsNull(c.DecRough,0) + IsNull(c.FactMake,0) as 应发数,       b.quantity as 实发数,       b.weight as 发出重量,       case b.PlateType         when 0 then ''正常''         when 1 then ''改电''         when 2 then ''返电''         else convert(varchar,b.PlateType)       end as 电镀类型,       b.Remark as 电镀备注'       set @condition=' from   OutPlateBaseMast a right join OutPlateBaseList b on a.OutBaseNo=b.OutBaseNo   inner join YieldOut c on b.YieldId=c.YieldId   inner join ProviderTable d on a.ProvideNo=d.providNo   inner join ProviderTable f on c.ProvidNo=f.ProvidNO   inner join TypeTable e on b.PlateNo=e.SerialNo and e.TypeId=''Y'''     --add datetime condition   set @condition=@condition + ' where b.OutDate>=''' + @outdate1 + ''' and b.OutDate <''' + @outdate2 + ''''   --add orderNo Condition   if @orderNo <>''     set @condition=@condition + ' and b.orderNo like ''' + @orderNo + '%'''   --add prodNo condition   if @ProdNo <>''     set @condition=@condition + ' and b.ProdNo like ''' + @ProdNo + '%'''   --add outBaseNo condition   if @outBaseNo <>''     set @condition =@condition + ' and b.outBaseNo=''' + @outBaseNo + ''''   if @provider <>''     set @condition=@condition + ' and d.providname=''' + @provider + ''''     if @state='0' --未审核     set @condition = @condition + ' and c.IfAudit=0'   else if @state='1' --已审核     set @condition = @condition + ' and c.IfAudit=1'   else if @state='2' --已打印     set @condition = @condition + ' and c.IfPrint=1'   --else default bypass '-1' to query all data rows   set @sql=@sql + @condition   print @sql   --return the formal result set   exec(@sql)     declare     @sql2 nvarchar(4000),@par nvarchar(4000),@v_orderNum int,@v_total_weight float,@v_record_count int   set @sql2=N'select @orderNum=count(distinct b.OrderNo),               @total_weight=sum(b.weight),               @record_count=count(*)'   set @sql2= @sql2 + convert(Nvarchar(4000),@condition)   print @sql2   set @par=N'@orderNum int output,@total_weight float output,@record_count int output'   exec sp_executesql @sql2,@par,@v_orderNum output,@v_total_weight output,@v_record_count output   --output the result   select @o_orderNum=@v_orderNum,@o_total_weight=@v_total_weight,@o_record_count=@v_record_count end; --查询分析器调用 declare   @ordernum int,@weight float,@recnum int exec Sp_PlateSend '2008-6-25','2008-6-26','','','','-1','',@ordernum output,@weight output,@recnum output select @ordernum as ordernum,@weight as total_weiht,@recnum as record_count --数据一大堆，几个输出参数都传了出来 //delphi调用 Sp_Send:TADOStoredProcedure   with Sp_Send do   begin     Parameters.ParamByName('@outdate1').Value:=outdate1;     Parameters.ParamByName('@outdate2').Value:=outdate2;     Parameters.ParamByName('@OrderNo').Value:=OrderNo;     Parameters.ParamByName('@ProdNo').Value:=ProdNo;     Parameters.ParamByName('@outBaseNo').Value:=outBaseNo;     Parameters.ParamByName('@state').Value:=state;     Parameters.ParamByName('@provider').Value:=provider;     Open;     //get the output parms of the stored procedure     OrderNum:=Parameters.ParamValues['@o_orderNum'];     Weight:=Parameters.ParamValues['@o_total_weight'];     RecCnt:=Parameters.ParamValues['@o_record_count'];   end;//end with except end; //end try -- 查询条件和使用查询分析器调用时一致,但没有数据返回，奇怪！！

lngdtommy · 2008-06-27

你先看下返回的DataSet.RecordCount是否为0。。。

gaobangsheng · 2008-06-27

我記得,好像,delphi中返回值好像是有點問題,我見意,你用SQL語句調用,然後, 采用select @o_total_weight, @o_record_count返回,用數據集捕獲 我之前就這麼做過

月满C楼 · 2008-06-28

是 delphi 的问题     Parameters.ParamByName('@outdate1').Value:=outdate1;     Parameters.ParamByName('@outdate2').Value:=outdate2; @outdate1和outdate1名字一样就没记录集返回了 我曾改为用动态参数那样调用，结果是一样 你先看下返回的DataSet.RecordCount是否为0。。。 我在查询分析器里一样的条件，一调用结果集马上出现在眼前呐 采用select @o_total_weight, @o_record_count返回,用數據集捕獲 我也使用了这种方式，结果还是那样。 后来改变了   outdate1,outdate2  变量名就搞定了。 但是在 delphi 下我没有使用过  nextrecordset ， C# 和 VB 下就用过，但还是没用起来    edit1.text:=sp_send.nextrecordset(i).Fields[0].value;  不得行

同时返回记录集，传出output参数的存储过程,delphi调用它总返回空集(6分)

月满C楼

Unregistered / Unconfirmed

lngdtommy

Unregistered / Unconfirmed

gaobangsheng

Unregistered / Unconfirmed

月满C楼

Unregistered / Unconfirmed

Similar threads