【SQL】分配函数一枚[AllotToTable]

--变量的声明和赋值需分开写
DECLARE @scale INT = 0 --需改为如下
DECLARE @scale INT; SET @scale = 0

--05不支持+=这样的复合运算符
SET @scale += 1 --需改为如下
SET @scale = @scale + 1

/*--------------------
函数：分配0.01
Author:AhDung
Update:201504211157
--------------------*/
ALTER FUNCTION dbo.AllotToTable(@num DECIMAL(16,4), @div TINYINT)
RETURNS @t TABLE(Val DECIMAL(16,4))
BEGIN
IF @num IS NULL OR @div IS NULL OR @div=0 RETURN

IF @div=1 --除数为1，直接返回1行
BEGIN
  INSERT @t SELECT @num
  RETURN
END

IF @num=0 --被除数为0，直接返回@div行0
BEGIN
  INSERT @t SELECT 0 FROM FMakeRows(@div)
  RETURN
END

/*得到@num的真实精度*/
DECLARE @scale INT=0, @numToInt DECIMAL(16,4)=@num
WHILE @numToInt % 1 <> 0
BEGIN
  SET @numToInt *= 10
  SET @scale += 1
END

/*填充元素（舍弃过的）*/
DECLARE @roundDownItem DECIMAL(16,4)=ROUND(@num / @div, @scale, 1)
INSERT @t SELECT @roundDownItem FROM FMakeRows(@div)

/*将余数均分到top n行。余数之所以不采用@num % @roundDownItem，是因为后者为1时就不对*/
DECLARE @mod DECIMAL(16,4) = @num - @roundDownItem*@@ROWCOUNT
IF @mod<>0
BEGIN
  DECLARE @pow INT = POWER(10, @scale)
  UPDATE TOP (CAST(ABS(@mod) * @pow AS INT)) @t
  SET Val += SIGN(@mod)/@pow
END

RETURN
END