--
-If Over Bill Case----------------------------------------------------------------------------------------------------------
declare @strInVoicetable nvarchar ( 1000 ), @strOrdertable nvarchar ( 1000 ), @Inv_NO nvarchar ( 1000 )
set @Inv_NO = ' 9010002999 '
set @strInVoicetable = ' fi_invoice_detail_2008 '
set @strOrdertable = ' order_detail_2008 '
DECLARE @OverSql NVARCHAR ( 4000 ), @OverParameter NVARCHAR ( 1000 )
declare @IsOverBill int
Select @OverSql = '
select @iIsOverBill =
case when (Convert(numeric(19,2),det.Med_Cost * fim.inv_exch) > Convert(numeric(19,2),(odet.UnitCost + odet.ProductionCost)))
or (Convert(numeric(19,2),det.AC1 * fim.inv_exch) > Convert(numeric(19,2),odet.AC1Amount))
then 1
else
case when @iIsOverBill = 1 then 1 else 0 end
end
from fi_invoice_master fim
left join ' + @strInVoicetable + ' det on fim.invoice_master_id = det.invoice_master_id
left join order_master ma on det.OrderNo=ma.OrderNo and det.OrderVersion=ma.OrderVersion
left join ' + @strOrdertable + ' odet on det.spotcode=odet.spotcode and det.order_detail_id = odet.order_detail_id
where fim.inv_no in( ' + @Inv_NO + ' )
' ,
@OverParameter = N ' @iIsOverBill int OUTPUT '
EXEC SP_EXECUTESQL @OverSql , @OverParameter , @IsOverBill OUTPUT
select @IsOverBill
-- ----------------------------------------------------------------------------------------------------------------------------
declare @strInVoicetable nvarchar ( 1000 ), @strOrdertable nvarchar ( 1000 ), @Inv_NO nvarchar ( 1000 )
set @Inv_NO = ' 9010002999 '
set @strInVoicetable = ' fi_invoice_detail_2008 '
set @strOrdertable = ' order_detail_2008 '
DECLARE @OverSql NVARCHAR ( 4000 ), @OverParameter NVARCHAR ( 1000 )
declare @IsOverBill int
Select @OverSql = '
select @iIsOverBill =
case when (Convert(numeric(19,2),det.Med_Cost * fim.inv_exch) > Convert(numeric(19,2),(odet.UnitCost + odet.ProductionCost)))
or (Convert(numeric(19,2),det.AC1 * fim.inv_exch) > Convert(numeric(19,2),odet.AC1Amount))
then 1
else
case when @iIsOverBill = 1 then 1 else 0 end
end
from fi_invoice_master fim
left join ' + @strInVoicetable + ' det on fim.invoice_master_id = det.invoice_master_id
left join order_master ma on det.OrderNo=ma.OrderNo and det.OrderVersion=ma.OrderVersion
left join ' + @strOrdertable + ' odet on det.spotcode=odet.spotcode and det.order_detail_id = odet.order_detail_id
where fim.inv_no in( ' + @Inv_NO + ' )
' ,
@OverParameter = N ' @iIsOverBill int OUTPUT '
EXEC SP_EXECUTESQL @OverSql , @OverParameter , @IsOverBill OUTPUT
select @IsOverBill
-- ----------------------------------------------------------------------------------------------------------------------------
我是数据库笨笨。