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

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

-- 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)

-- 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. 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 只返回最后一条查询的结果集