SQL Server 返回结果集的几种方式

最近用到了SQL Server的几种结果集返回方法，这里整理如下（注：使用SQL Server 的  AdventureWorks2008 示例数据库）

1. 使用 Table Function 返回结果集

-- 1. table function
use AdventureWorks2008
go

if exists (
       select 1
       from   sys.objects
              where [type] in (N'TF' ,N'IF' ,N'FN')
       and name = 'fn_getPerson'
   )
    drop function dbo.fn_getPerson
go

create function dbo.fn_getPerson
(
    @EntityID int
)
returns @result table (EntityID int ,PersonType varchar(10) ,FirstName varchar(50) ,LastName varchar(50))
as
begin
    insert into @result
      (
        EntityID
       ,PersonType
       ,FirstName
       ,LastName
      )
    select BusinessEntityID
          ,PersonType
          ,FirstName
          ,LastName
    from   Person.Person
           where BusinessEntityID = @EntityID

    return  -- return must be last sql
end
go


print('dbo.fn_getPerson has been created.')

-- select * from dbo.fn_getPerson(1)

2. 使用 Inline Function 返回结果集

-- 3. inline function
use AdventureWorks2008
go

if exists (
       select 1
       from   sys.objects
              where [type] in (N'TF' ,N'IF' ,N'FN')
       and name = 'fn_getPerson2'
   )
    drop function dbo.fn_getPerson2
go

create function dbo.fn_getPerson2
(
    @EntityID int
)
returns table
as
    return
    select BusinessEntityID
          ,PersonType
          ,FirstName
          ,LastName
    from   Person.Person
           where BusinessEntityID = @EntityID
go

print('dbo.fn_getPerson2 has been created.')

-- select * from dbo.fn_getPerson2(1)

3. 使用存储过程返回结果集

-- 3. procedure
use AdventureWorks2008
go

if exists (
       select 1
       from   sys.procedures
        where name = 'usp_getPerson'
   )
    drop procedure dbo.usp_getPerson
go

create procedure dbo.usp_getPerson
(
    @EntityID int
)
as
begin
    --....... do some process

    -- result of last query will return
    select BusinessEntityID
          ,PersonType
          ,FirstName
          ,LastName
    from   Person.Person
           where BusinessEntityID = @EntityID

end
go


print('dbo.usp_getPerson has been created.')

-- exec dbo.usp_getPerson @EntityID = 1

注：SQL Server 只返回最后一条查询的结果集