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

  • 主题发起人 主题发起人 月满C楼
  • 开始时间 开始时间

月满C楼

Unregistered / Unconfirmed
GUEST, unregistred user!
Create Procedure Sp_PlateSend( <br>&nbsp; @outdate1 varchar(200), <br>&nbsp; @outdate2 varchar(200), <br>&nbsp; @OrderNo varchar(200), <br>&nbsp; @ProdNo varchar(200), <br>&nbsp; @outBaseNo varchar(200), <br>&nbsp; @state varchar(200), <br>&nbsp; @provider varchar(200), <br>&nbsp; @o_orderNum int output, <br>&nbsp; @o_total_weight float output, <br>&nbsp; @o_record_count int output <br>) as <br>begin <br>&nbsp; declare <br>&nbsp; &nbsp; @sql varchar(4000),@condition varchar(4000) <br>&nbsp; set @sql='select <br>&nbsp; &nbsp; &nbsp; b.OutBaseNo as 电镀单号, <br>&nbsp; &nbsp; &nbsp; d.ProvidName as 计划电镀厂商, <br>&nbsp; &nbsp; &nbsp; d.ProvidName as 实发电镀厂商, <br>&nbsp; &nbsp; &nbsp; c.ShipDate as 发货日期, <br>&nbsp; &nbsp; &nbsp; c.PlateStart as 计划电镀日期, <br>&nbsp; &nbsp; &nbsp; b.OrderNo as 订单号, <br>&nbsp; &nbsp; &nbsp; b.ProdNo as 产品编码, <br>&nbsp; &nbsp; &nbsp; e.SerialName as 电镀色, <br>&nbsp; &nbsp; &nbsp; b.ProdName as 电镀名称, <br>&nbsp; &nbsp; &nbsp; b.Bag as 包数, <br>&nbsp; &nbsp; &nbsp; IsNull(c.DecRough,0) + IsNull(c.FactMake,0) as 应发数, <br>&nbsp; &nbsp; &nbsp; b.quantity as 实发数, <br>&nbsp; &nbsp; &nbsp; b.weight as 发出重量, <br>&nbsp; &nbsp; &nbsp; case b.PlateType <br>&nbsp; &nbsp; &nbsp; &nbsp; when 0 then ''正常'' <br>&nbsp; &nbsp; &nbsp; &nbsp; when 1 then ''改电'' <br>&nbsp; &nbsp; &nbsp; &nbsp; when 2 then ''返电'' <br>&nbsp; &nbsp; &nbsp; &nbsp; else convert(varchar,b.PlateType) <br>&nbsp; &nbsp; &nbsp; end as 电镀类型, <br>&nbsp; &nbsp; &nbsp; b.Remark as 电镀备注' <br>&nbsp; &nbsp; <br>&nbsp; set @condition=' from <br>&nbsp; OutPlateBaseMast a right join OutPlateBaseList b on a.OutBaseNo=b.OutBaseNo <br>&nbsp; inner join YieldOut c on b.YieldId=c.YieldId <br>&nbsp; inner join ProviderTable d on a.ProvideNo=d.providNo <br>&nbsp; inner join ProviderTable f on c.ProvidNo=f.ProvidNO <br>&nbsp; inner join TypeTable e on b.PlateNo=e.SerialNo and e.TypeId=''Y''' <br>&nbsp; <br>&nbsp; --add datetime condition <br>&nbsp; set @condition=@condition + ' where b.OutDate&gt;=''' + @outdate1 + ''' and b.OutDate &lt;''' + @outdate2 + '''' <br>&nbsp; --add orderNo Condition <br>&nbsp; if @orderNo &lt;&gt;'' <br>&nbsp; &nbsp; set @condition=@condition + ' and b.orderNo like ''' + @orderNo + '%''' <br>&nbsp; --add prodNo condition <br>&nbsp; if @ProdNo &lt;&gt;'' <br>&nbsp; &nbsp; set @condition=@condition + ' and b.ProdNo like ''' + @ProdNo + '%''' <br>&nbsp; --add outBaseNo condition <br>&nbsp; if @outBaseNo &lt;&gt;'' <br>&nbsp; &nbsp; set @condition =@condition + ' and b.outBaseNo=''' + @outBaseNo + '''' <br>&nbsp; if @provider &lt;&gt;'' <br>&nbsp; &nbsp; set @condition=@condition + ' and d.providname=''' + @provider + '''' <br>&nbsp; <br>&nbsp; if @state='0' --未审核 <br>&nbsp; &nbsp; set @condition = @condition + ' and c.IfAudit=0' <br>&nbsp; else if @state='1' --已审核 <br>&nbsp; &nbsp; set @condition = @condition + ' and c.IfAudit=1' <br>&nbsp; else if @state='2' --已打印 <br>&nbsp; &nbsp; set @condition = @condition + ' and c.IfPrint=1' <br>&nbsp; --else default bypass '-1' to query all data rows <br>&nbsp; set @sql=@sql + @condition <br><br>&nbsp; print @sql <br>&nbsp; --return the formal result set <br>&nbsp; exec(@sql) <br>&nbsp; <br>&nbsp; declare <br>&nbsp; &nbsp; @sql2 nvarchar(4000),@par nvarchar(4000),@v_orderNum int,@v_total_weight float,@v_record_count int <br>&nbsp; set @sql2=N'select @orderNum=count(distinct b.OrderNo), <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @total_weight=sum(b.weight), <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @record_count=count(*)' <br>&nbsp; set @sql2= @sql2 + convert(Nvarchar(4000),@condition) <br>&nbsp; print @sql2 <br>&nbsp; set @par=N'@orderNum int output,@total_weight float output,@record_count int output' <br>&nbsp; exec sp_executesql @sql2,@par,@v_orderNum output,@v_total_weight output,@v_record_count output <br><br>&nbsp; --output the result <br>&nbsp; select @o_orderNum=@v_orderNum,@o_total_weight=@v_total_weight,@o_record_count=@v_record_count <br>end; <br><br>--查询分析器调用 <br>declare <br>&nbsp; @ordernum int,@weight float,@recnum int <br>exec Sp_PlateSend '2008-6-25','2008-6-26','','','','-1','',@ordernum output,@weight output,@recnum output <br><br>select @ordernum as ordernum,@weight as total_weiht,@recnum as record_count <br>--数据一大堆,几个输出参数都传了出来 <br><br>//delphi调用 Sp_Send:TADOStoredProcedure <br>&nbsp; with Sp_Send do <br>&nbsp; begin <br>&nbsp; &nbsp; Parameters.ParamByName('@outdate1').Value:=outdate1; <br>&nbsp; &nbsp; Parameters.ParamByName('@outdate2').Value:=outdate2; <br>&nbsp; &nbsp; Parameters.ParamByName('@OrderNo').Value:=OrderNo; <br>&nbsp; &nbsp; Parameters.ParamByName('@ProdNo').Value:=ProdNo; <br>&nbsp; &nbsp; Parameters.ParamByName('@outBaseNo').Value:=outBaseNo; <br>&nbsp; &nbsp; Parameters.ParamByName('@state').Value:=state; <br>&nbsp; &nbsp; Parameters.ParamByName('@provider').Value:=provider; <br>&nbsp; &nbsp; Open; <br>&nbsp; &nbsp; //get the output parms of the stored procedure <br>&nbsp; &nbsp; OrderNum:=Parameters.ParamValues['@o_orderNum']; <br>&nbsp; &nbsp; Weight:=Parameters.ParamValues['@o_total_weight']; <br>&nbsp; &nbsp; RecCnt:=Parameters.ParamValues['@o_record_count']; <br>&nbsp; end;//end with <br>except <br>end; //end try <br>-- 查询条件和使用查询分析器调用时一致,但没有数据返回,奇怪!!
 
你先看下返回的DataSet.RecordCount是否为0。。。
 
我記得,好像,delphi中返回值好像是有點問題,我見意,你用SQL語句調用,然後,<br>采用select @o_total_weight, @o_record_count返回,用數據集捕獲<br>我之前就這麼做過
 
是 delphi 的问题<br>&nbsp; &nbsp; Parameters.ParamByName('@outdate1').Value:=outdate1; <br>&nbsp; &nbsp; Parameters.ParamByName('@outdate2').Value:=outdate2; <br>@outdate1和outdate1名字一样就没记录集返回了<br>我曾改为用动态参数那样调用,结果是一样<br><br>你先看下返回的DataSet.RecordCount是否为0。。。 <br>我在查询分析器里一样的条件,一调用结果集马上出现在眼前呐<br><br>采用select @o_total_weight, @o_record_count返回,用數據集捕獲<br>我也使用了这种方式,结果还是那样。<br>后来改变了 &nbsp; outdate1,outdate2 &nbsp;变量名就搞定了。<br><br>但是在 delphi 下我没有使用过 &nbsp;nextrecordset , C# 和 VB 下就用过,但还是没用起来<br>&nbsp; &nbsp;edit1.text:=sp_send.nextrecordset(i).Fields[0].value;<br>&nbsp;不得行
 
后退
顶部