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