1、标量函数
CREATE FUNCTION RMB_AMOUNT(@currency varchar(32),@amount numeric(10,2),@month int)
returns numeric(10,2)--设置返回值
AS
BEGIN
DECLARE @result numeric(10,2)
DECLARE @start_date datetime
SET @start_date = cast((DATENAME (yyyy,Getdate())+'-'+cast(@month as varchar)+'-'+'1') as datetime)
select top(1) @result = T.rate from v_currency_rate T where t.CURRENCY_ABBREVIATION = @currency and T.START_DATE <=@start_date order by t.START_DATE desc
return @amount*@result
END
2、带有返回结果集
ALTER FUNCTION [dbo].[getDept](@deptId varchar(32))
returns @t table(dept_id varchar(32),dept_name varchar(128),display_order varchar(32))
as
begin
with CTE as
(
select * from bm533_sys.dbo.spsys_department where dept_id= @deptId
UNION ALL
(SELECT a.* from bm533_sys.dbo.spsys_department as a inner join
CTE as b on a.parent_dept_id=b.dept_id
)
)
insert into @t (dept_id,dept_name,display_order) ( SELECT RTRIM(dept_id) dept_id,dept_name,display_order FROM CTE where dept_name like '%项目部%' )
return
end