表值函数

简单点的直接返回查询的table对象

CREATE FUNCTION Fun_GetReportNews(@type varchar(10))
RETURNS TABLE
AS
RETURN
(
SELECT  message,tablenames from tb_error WHERE tablenames = @type
)
调用：SELECT * FROM dbo.Fun_GetReportNews('')

 

复杂点的 先创建一张临时表再返回表对象

CREATE FUNCTION FUN_GetInfoList(@type varchar(10))
RETURNS @Table TABLE(id int identity(1,1),name nvarchar(100),intime datetime)
AS
BEGIN

INSERT @Table SELECT lastname,intime FROM tb_orderuserinfo WHERE city = @type

RETURN
END
调用 ：select * from dbo.FUN_GetInfoList('APO')