sql server 遇到以零作除数错误。

[accpchenjun]

下面这个语句报“遇到以零作除数错误。” 请大侠帮忙排错 改造

SELECT				dbo.Bas_Category.Cat_TaxRate,dbo.Sal_Order.Sal_SalDate,
					dbo.Sal_Order.Sal_InvAmount as hsje,
					(dbo.Sal_Order.Sal_SalQty*dbo.Sal_OrderDetl.Sal_StoPrice) as hscb,
					(dbo.Sal_Order.Sal_InvAmount-(dbo.Sal_Order.Sal_SalQty*dbo.Sal_OrderDetl.Sal_StoPrice))as hsml,
					CONVERT(Numeric(18, 2),(((dbo.Sal_Order.Sal_InvAmount-(dbo.Sal_Order.Sal_SalQty*dbo.Sal_OrderDetl.Sal_StoPrice))/dbo.Sal_Order.Sal_InvAmount)*100))as hsmll,
					dbo.Sal_Order.Sal_InvAmount/dbo.Bas_Category.Cat_TaxRate as bhsje,
					(dbo.Sal_Order.Sal_SalQty*dbo.Sal_OrderDetl.Sal_StoPrice)/dbo.Bas_Category.Cat_TaxRate as bhsjb,
					(dbo.Sal_Order.Sal_InvAmount/dbo.Bas_Category.Cat_TaxRate)-((dbo.Sal_Order.Sal_SalQty*dbo.Sal_OrderDetl.Sal_StoPrice)/dbo.Bas_Category.Cat_TaxRate) as bhsml,
					CONVERT(Numeric(18, 2),(dbo.Sal_Order.Sal_InvAmount/dbo.Bas_Category.Cat_TaxRate)-((dbo.Sal_Order.Sal_SalQty*dbo.Sal_OrderDetl.Sal_StoPrice)/dbo.Bas_Category.Cat_TaxRate)/(dbo.Sal_Order.Sal_InvAmount/dbo.Bas_Category.Cat_TaxRate)) as bhsmll
FROM         dbo.Bas_Category INNER JOIN
                      dbo.Sal_OrderDetl ON dbo.Bas_Category.Cat_ID = dbo.Sal_OrderDetl.Cat_ID INNER JOIN
                      dbo.Sal_Order ON dbo.Sal_OrderDetl.Sal_ID = dbo.Sal_Order.Sal_ID
WHERE     (dbo.Sal_Order.Sal_SalStatus = N'已出库') AND (dbo.Sal_OrderDetl.Sal_Datamark = N'正常')

[cjzm83]

dbo.Bas_Category.Cat_TaxRate 替换为 case when dbo.Bas_Category.Cat_TaxRate = 0 then null else dbo.Bas_Category.Cat_TaxRate end

[hovy_yang]

select N'看了头晕' as 结果

[feilniu]

SELECT
	bc.Cat_TaxRate,
	so.Sal_SalDate,
	hsje = so.Sal_InvAmount,
	hscb = so.Sal_SalQty * sod.Sal_StoPrice,
	hsml = so.Sal_InvAmount - (so.Sal_SalQty * sod.Sal_StoPrice),
	hsmll = CONVERT(Numeric(18,2),(((so.Sal_InvAmount - (so.Sal_SalQty * sod.Sal_StoPrice)) / so.Sal_InvAmount) * 100)),
	bhsje = so.Sal_InvAmount / bc.Cat_TaxRate,
	bhsjb = (so.Sal_SalQty * sod.Sal_StoPrice) / bc.Cat_TaxRate,
	bhsml = (so.Sal_InvAmount / bc.Cat_TaxRate) - ((so.Sal_SalQty * sod.Sal_StoPrice) / bc.Cat_TaxRate),
	bhsmll = CONVERT(Numeric(18,2),(so.Sal_InvAmount / bc.Cat_TaxRate) - ((so.Sal_SalQty * sod.Sal_StoPrice) / bc.Cat_TaxRate) / (so.Sal_InvAmount / bc.Cat_TaxRate))
FROM dbo.Bas_Category bc
INNER JOIN dbo.Sal_OrderDetl sod
ON bc.Cat_ID = sod.Cat_ID
INNER JOIN dbo.Sal_Order so
ON sod.Sal_ID = so.Sal_ID
WHERE so.Sal_SalStatus = N'已出库'
	AND sod.Sal_Datamark = N'正常'

看除号后面的哪个表达式可能为0，然后改成：NULLIF(表达式,0)

[feilniu]

如果LZ的代码是程序生成的，那无可厚非。如果是自己写的，有这么几点建议：
1. 使用表别名，可以减少代码量，并且提高代码可读性。
2. 合理使用括号，但不要过度使用。过度使用和不使用一样，都会降低代码可读性。
3. 代码缩进和布局调整一下，自己和别人看起来都容易。

事实上，即使是程序生成代码，格式上也是可以适当加以控制的。毕竟生成的代码也要查错和调试。

[claro]

http://blog.csdn.net/claro/archive/2009/01/06/3720863.aspx建议读：避免零做除数
http://blog.csdn.net/claro/archive/2009/01/06/3720863.aspx