SqlServer创建函数实现分页

内联表值函数是一种可重用的表表达式，能够支持输入参数。除了支持输入参数以外，内联表值函数在其他方面都与视图相似。（可以将内联表值函数看作是一种参数化的视图，尽管没有这种正式的说法）。

表值函数有分为内联表值函数（相当于参数化的视图）和多语句表值函数，二者原理一样，功能略有不同当然写法也就略有不同。

内联表值函数和多语句表值函数的区别：  

    内联表值函数，RETURNS 子句只包含关键字 table。不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句 的结果集的格式设置。

    内联表值函数的 函数体 不用 BEGIN 和 END 分隔。

    内联表值函数的 RETURN 子句在括号中包含单个 SELECT 语句。SELECT 语句的结果集构成函数所返回的表。其使用的SELECT 语句与视图中使用的 SELECT 语句受到相同的限制。

 

 语法:

create function fn_函数名

(@变量 as 类型,@变量2 as 类型)

returns table

as

return

(

    结果集

)

 

例子:

go
create function fn_fenye
(@pageIndex as int, @pageSize as int) returns table
as
    return
        select
            * 
        from
--vw_FullStu 是一个视图,在视图那部分里有定义,函数内可以调用视图
            vw_FullStu
        where
            num between (@pageIndex - 1) * @pageSize + 1
            and
            @pageIndex * @pageSize;
go
--调用
select * from fn_fenye(3,7);

 

--定义：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  zyp
-- Create date: 2012-01-09
-- Description: 盗版我们更专业!!!
-- =============================================
Create FUNCTION fn_FunGetMaterialList
( 
 @atrid int
)
RETURNS TABLE 
AS
RETURN 
(
 select * from Material where AtrID=@atrid
)
GO

--使用：

select * from fn_FunGetMaterialList(5)

 

顾名思义：表值函数返回的是表，而标量值函数可以返回基类型

1. 表值函数

 

用户定义表值函数返回 table 数据类型。对于内联表值函数，没有函数主体；表是单个 SELECT 语句的结果集。

以下示例创建了一个内联表值函数。此函数的输入参数为客户（商店）ID，而返回 ProductID、Name 以及 YTD Total（销售到商店的每种产品的本年度节截止到现在的销售总额）列。

USE AdventureWorks;
GO
CREATE FUNCTION Sales.fn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
    FROM Production.Product AS P 
      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

--以下示例调用此函数并指定客户 ID 为 602
 

SELECT * FROM Sales.fn_SalesByStore (602);

 

 

对于多语句表值函数，在 BEGIN...END 语句块中定义的函数体包含一系列 Transact-SQL 语句，这些语句可生成行并将其插入将返回的表中。

以下示例创建了一个表值函数。此函数具有一个输入参数 EmployeeID 而返回直接或间接向指定员工报告的所有员工的列表。

 

USE AdventureWorks;
GO
CREATE FUNCTION dbo.fn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
--在以下示例中，调用了此函数。
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.fn_FindReports(109)
ORDER BY Sort;

内联表值型函数（Inline table-valued functions）以表的形式返回一个返回值，即它返回的是一个表内联表值型函数没有由BEGIN-END 语句括起来的函数体。其返回的表由一个位于RETURN 子句中的SELECT 命令段从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。

 

以下是一个例子,是将一个人事考勤排班的数据显示到Grid,中间嵌套了其他自定义函数

CREATE FUNCTION   HR_SQL_A03 ()
RETURNS  table 
AS 
return
(
 Select  top 100 percent
      case isnull(status,0) when 0 then '' when 1 then '已审核' when 2 then '已审批'

       when 3  then '核对' end as check_status_name ,
       Class_No,Class_Name,Time_Work,
       In1_Min,Time1_In,In1_Max,

       dbo.get_100_name('day_type',Time1_In_Day) as Time1_In_Day_name ,
       Out1_Min,time1_out,Out1_Max,

       dbo.get_100_name('day_type',Time1_out_Day) as Time1_out_Day_name ,
       dbo.get_100_name('work_type',Time1_Type) as time1_type_name,

       other_1,time1_work,time1_rest,
       In2_Min,Time2_In,In2_Max,

       dbo.get_100_name('day_type',Time2_In_Day) as time2_in_day_name ,
       Out2_Min,time2_out,Out2_Max,

       dbo.get_100_name('day_type',Time2_out_Day) as time2_out_day_name,
       dbo.get_100_name('work_type',Time2_Type) as time2_type_name,

       Other_2,time2_work,time2_rest, 
       In3_Min,Time3_In,In3_Max,

       dbo.get_100_name('day_type',Time3_In_Day) as time3_in_day_name ,
       Out3_Min,time3_out,Out3_Max,

       dbo.get_100_name('day_type',Time3_out_Day) as time3_out_day_name,
       dbo.get_100_name('work_type',Time3_Type) as time3_type_name,

       Other_3,time3_work,time3_rest,
       in4_Min,Time4_In,In4_Max,

       dbo.get_100_name('day_type',Time4_In_Day) as time4_in_day_name ,
       Out4_Min,time4_out,Out4_Max,

       dbo.get_100_name('day_type',time4_out_day) as Time4_out_Day_name,
       dbo.get_100_name('work_type',Time4_Type) as time4_type_name,

       Other_4,time4_work,time4_rest, 
       Upd_Name ,Upd_Date , check_1,date_1, check_2,date_2, check_3,date_3 ,
       isnull(status,0) as status ,
       Auto1_a,Auto1_b,Auto2_a,Auto2_b,Auto3_a,Auto3_b,Auto4_a,Auto4_b,
       check1,zheng_1,check2,zheng_2,check3,zheng_3,check4,zheng_4, 
       Time1_In_Day,Time1_out_Day,Time2_In_Day, Time2_out_Day,
       Time3_In_Day,Time3_out_Day,Time4_In_Day,Time4_out_Day, 
       Time1_Type,Time2_Type,Time3_Type,Time4_Type
From hrstp08 
Order By Class_No
)