函数与直接SQL语句有什么区别?

[ghosthjt]

同样的SQL语句:
select isnull(sum(case when rtrim(tip)='普通事假' then amount else 0 end),0) as 普通事假工资,
isnull(sum(case when rtrim(tip)='连续事假' then amount else 0 end ),0) as 连续事假工资,
isnull(sum(case when rtrim(tip)='无薪病假' then amount else 0 end ),0) as 病假工资,
isnull(sum(case when rtrim(tip)= '连续无薪病假' then amount else 0 end ),0) as 连续病假工资,
isnull(sum(case when rtrim(tip) not in( '连续无薪病假','无薪病假','连续事假','普通事假') then amount else 0 end ),0) as 其它扣工资
from punish_ds where datediff(m,happenddate,@date)=0 and pid=@pid

写成了函数:
ALTER function dbo.GetTotalDS(@pid varchar,@date datetime)
returns table
as
return
(
select isnull(sum(case when rtrim(tip)='普通事假' then amount else 0 end),0) as 普通事假工资,
isnull(sum(case when rtrim(tip)='连续事假' then amount else 0 end ),0) as 连续事假工资,
isnull(sum(case when rtrim(tip)='无薪病假' then amount else 0 end ),0) as 病假工资,
isnull(sum(case when rtrim(tip)= '连续无薪病假' then amount else 0 end ),0) as 连续病假工资,
isnull(sum(case when rtrim(tip) not in( '连续无薪病假','无薪病假','连续事假','普通事假') then amount else 0 end ),0) as 其它扣工资
from punish_ds where datediff(m,happenddate,@date)=0 and pid=@pid )

其怪的是这个函数不能正确的工作，以下语句作为试验，在查询分析器里：

declare @pid varchar(50)
declare @date datetime
set @date=getdate();
select @pid=pid from jobwanter_basic where 姓名='洪建涛'


select isnull(sum(case when rtrim(tip)='普通事假' then amount else 0 end),0) as 普通事假工资,
isnull(sum(case when rtrim(tip)='连续事假' then amount else 0 end ),0) as 连续事假工资,
isnull(sum(case when rtrim(tip)='无薪病假' then amount else 0 end ),0) as 病假工资,
isnull(sum(case when rtrim(tip)= '连续无薪病假' then amount else 0 end ),0) as 连续病假工资,
isnull(sum(case when rtrim(tip) not in( '连续无薪病假','无薪病假','连续事假','普通事假') then amount else 0 end ),0) as 其它扣工资
from punish_ds where datediff(m,happenddate,@date)=0 and pid=@pid

select * from dbo.gettotalds(@pid,@date)


两个查询返回的结果完全不同
第一个：17.925430297851562 0.0 59.751434326171875 0.0 0.0
第二个: 0.0 0.0 0.0 0.0 0.0

也就是说SQL写成函数之后不能正确工作，哪位达人能解释一下哪里错了，怎么改.谢谢了

[vivianfdlpw]

ALTER function dbo.GetTotalDS(@pid varchar,@date datetime)

================>
ALTER function dbo.GetTotalDS(@pid varchar(50),@date datetime)

[ghosthjt]

找到问题了,由于一时疏忽犯了楼上说的低级错误.谢谢楼上

[MorningTea]

就是楼上上的那样，以前我也有犯过这样的错误，
简单测试例子，
declare @tt as varchar
set @tt = 'yyy'
print 'tt:Length 1' + convert(varchar,len(@tt))

declare @kk as varchar(10)
set @kk = 'yyy'
print 'kk:Length 1' + convert(varchar,len(@kk))
结果：
tt:Length-->1
kk:Length-->3