函数

语句格式
---------------------------

--Test 数据
 if exists (select * from sysobjects where id = OBJECT_ID('[t]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [t]
 CREATE TABLE [t] (  [id] [int] NOT NULL , [T1] [nvarchar] (50) NULL , [T2] [nvarchar] (50) NULL )

 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 8 , 'A' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 9 , 'D' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 10 , 'F' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 11 , 'D' , '002' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 12 , 'F' , '002' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 13 , 'F' , '003' )

--带默认参数的函数
DROP FUNCTION Temp_Fun
CREATE FUNCTION Temp_Fun(@A Int =5, @B Int =3)RETURNS Int
as
BEGIN
   Return @A * @B
END

select dbo.Temp_Fun(DEFAULT,DEFAULT)
select dbo.Temp_Fun(8,DEFAULT)

--表值函数
DROP FUNCTION Temp_Tab
CREATE FUNCTION Temp_Tab(@Temp_T1 varchar(10))RETURNS Table
as
RETURN(
   select * from T where T1=@Temp_T1
)
select * from dbo.Temp_Tab('F')

--多表值
CREATE FUNCTION Temp_Tab()RETURNS @Temp_T Table
 (
    TID int,
    X1 varchar(10),
    X2 varchar(10)
  )
as
BEGIN
  INSERT @Temp_T(TID,X1,X2) SELECT * FROM T WHERE T1='A'
  INSERT @Temp_T(TID,X1,X2) SELECT * FROM T WHERE T1='F'
  RETURN
END

select * from dbo.Temp_Tab()