SQL SERVER函数

来源：互联网发布：集思宝测量软件编辑：程序博客网时间：2024/05/18 17:59

1、标量函数

CREATE FUNCTION RMB_AMOUNT(@currency varchar(32),@amount numeric(10,2),@month int)returns numeric(10,2)--设置返回值ASBEGIN    DECLARE @result numeric(10,2)    DECLARE @start_date datetimeSET @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 descreturn @amount*@resultEND

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= @deptIdUNION 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