为什么-----这个自定义函数的返回值是0

[cowbo2]

应该是2000多才对啊..是不是哪不对了

测试代码:
select dbo.finddisc(60,3600,8)

CREATE FUNCTION FindDisc(@Deadline int,@price decimal(18,2),@UsedMonth int)
RETURNS decimal(18,2) AS 

BEGIN 
declare @Str decimal(18,2)
declare @uyear int,@desc decimal(18,2)
declare @i int,@num decimal(18,2),@snum decimal(18,2),@tot decimal(18,2)
declare @tt table (i int,num decimal(18,2),snum decimal(18,2),nowpric decimal(18,2))
--初始化变量
set @i=1
set @uyear= @Deadline/12
set @desc=2/@uyear
set @num=@price*@desc/12
set @snum=@num


begin
while @i<=(@uyear*12)
begin
insert @tt select @i,@num,@snum,@price-@num
set @snum=@snum+@num
set @price=@price-@num 

    if @i%12=0 and  (@i-1)/12<@uyear-2 --1-3年
     begin
         if (@i+1)%12>@i%12
            begin
                set @snum=@snum-@num
                set @num=@price*@desc/12
                set @snum=@snum+@num
            end
    end

    if @i%12=0 and  (@i-1)/12=2 --最后两年
        begin
                set @snum=@snum-@num
                set @num=@price/24
                set @snum=@snum+@num
        end
                
    set @i=@i+1

end
end

select @Str=(select top 1 snum from @tt where i=@UsedMonth)

return @Str


END

[dawugui]

select top 1 snum from @tt where i=@UsedMonth

表名动态必须使用动态SQL.

参考如下最后一段.

--动态sql语句基本语法

 
1 :普通SQL语句可以用Exec执行 

eg: Select * from tableName 
Exec('select * from tableName') 
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 

2:字段名，表名，数据库名之类作为变量时，必须用动态SQL 

eg: 
declare @fname varchar(20) 
set @fname = 'FiledName' 
Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 

当然将字符串改成变量的形式也可 
declare @fname varchar(20) 
set @fname = 'FiledName' --设置字段名 

declare @s varchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句会报错 

declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) 
set @s = 'select ' + @fname + ' from tableName' 
Exec(@s) -- 成功 
exec sp_executesql @s -- 此句正确 

3. 输出参数 
declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select count(*) from tableName' 
exec(@sqls) 
--如何将exec执行结果放入变量中？ 

declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select @a=count(*) from tableName ' 
exec sp_executesql @sqls,N'@a int output',@num output 
select @num 

[--小F--]

有点长 看看是哪里没有赋值

[--小F--]

[Quote=引用 1 楼 dawugui 的回复:]
select top 1 snum from @tt where i=@UsedMonth

表名动态必须使用动态SQL.

参考如下最后一段.

--动态sql语句基本语法

SQL code

1 :普通SQL语句可以用Exec执行

eg: Select * from tableName
Exec('select * from tableName')
Ex……
[/Quote]
函数里面可以用动态?

[dawugui]

[Quote=引用 3 楼 fredrickhu 的回复:]
函数里面可以用动态?[/Quote]
不能.

[cowbo2]

楼上各位,如果不能,那如何解决这个问题啊..

这个自定义函数的结果,要能让其它视图调用才行的啊..

[leo_lesley]

--    下面是你表变量输出的记录，按照你的输入参数得到前36个月的值都是 0.0
1	.00	.00	3600.00
2	.00	.00	3600.00
3	.00	.00	3600.00
4	.00	.00	3600.00
5	.00	.00	3600.00
6	.00	.00	3600.00
7	.00	.00	3600.00
8	.00	.00	3600.00
9	.00	.00	3600.00
10	.00	.00	3600.00
11	.00	.00	3600.00
12	.00	.00	3600.00
13	.00	.00	3600.00
14	.00	.00	3600.00
15	.00	.00	3600.00
16	.00	.00	3600.00
17	.00	.00	3600.00
18	.00	.00	3600.00
19	.00	.00	3600.00
20	.00	.00	3600.00
21	.00	.00	3600.00
22	.00	.00	3600.00
23	.00	.00	3600.00
24	.00	.00	3600.00
25	.00	.00	3600.00
26	.00	.00	3600.00
27	.00	.00	3600.00
28	.00	.00	3600.00
29	.00	.00	3600.00
30	.00	.00	3600.00
31	.00	.00	3600.00
32	.00	.00	3600.00
33	.00	.00	3600.00
34	.00	.00	3600.00
35	.00	.00	3600.00
36	.00	.00	3600.00
37	150.00	150.00	3450.00
38	150.00	300.00	3300.00
39	150.00	450.00	3150.00
40	150.00	600.00	3000.00
41	150.00	750.00	2850.00
42	150.00	900.00	2700.00
43	150.00	1050.00	2550.00
44	150.00	1200.00	2400.00
45	150.00	1350.00	2250.00
46	150.00	1500.00	2100.00
47	150.00	1650.00	1950.00
48	150.00	1800.00	1800.00
49	150.00	1950.00	1650.00
50	150.00	2100.00	1500.00
51	150.00	2250.00	1350.00
52	150.00	2400.00	1200.00
53	150.00	2550.00	1050.00
54	150.00	2700.00	900.00
55	150.00	2850.00	750.00
56	150.00	3000.00	600.00
57	150.00	3150.00	450.00
58	150.00	3300.00	300.00
59	150.00	3450.00	150.00
60	150.00	3600.00	.00

[-狙击手-]

set @desc=2/@uyear

--->

set @desc=2.0/@uyear

[-狙击手-]

 
CREATE FUNCTION FindDisc(@Deadline int,@price decimal(18,2),@UsedMonth int)
RETURNS  decimal(18,2) AS 

BEGIN 
declare @Str decimal(18,2)
declare @uyear int,@desc decimal(18,2)
declare @i int,@num decimal(18,2),@snum decimal(18,2),@tot decimal(18,2)
declare @tt table (i int,num decimal(18,2),snum decimal(18,2),nowpric decimal(18,2))
--初始化变量
set @i=1
set @uyear= @Deadline/12
set @desc=2.0/@uyear
set @num=@price*@desc/12
set @snum=@num


begin
while @i<=(@uyear*12)
begin
insert @tt select @i,@num,@snum,@price-@num
 
set @snum=@snum+@num
set @price=@price-@num 

    if @i%12=0 and  (@i-1)/12<@uyear-2 --1-3年
     begin
         if (@i+1)%12>@i%12
            begin
                set @snum=@snum-@num
                set @num=@price*@desc/12
                set @snum=@snum+@num
            end
    end

    if @i%12=0 and  (@i-1)/12=2 --最后两年
        begin
                set @snum=@snum-@num
                set @num=@price/24
                set @snum=@snum+@num
        end
                
    set @i=@i+1

end
end
 select top 1 @Str=snum from @tt where i=@UsedMonth

return @Str
 


END

go
select dbo.finddisc(60,3600,8)
 ---
960
drop function FindDisc