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

来源：互联网发布：mac os pc装机编辑：程序博客网时间：2024/06/09 02:27

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

1. 使用 Table Function 返回结果集

-- 1. table functionuse AdventureWorks2008goif exists (       select 1       from   sys.objects              where [type] in (N'TF' ,N'IF' ,N'FN')       and name = 'fn_getPerson'   )    drop function dbo.fn_getPersongocreate function dbo.fn_getPerson(    @EntityID int)returns @result table (EntityID int ,PersonType varchar(10) ,FirstName varchar(50) ,LastName varchar(50))asbegin    insert into @result      (        EntityID       ,PersonType       ,FirstName       ,LastName      )    select BusinessEntityID          ,PersonType          ,FirstName          ,LastName    from   Person.Person           where BusinessEntityID = @EntityID    return  -- return must be last sqlendgoprint('dbo.fn_getPerson has been created.')-- select * from dbo.fn_getPerson(1)

2. 使用 Inline Function 返回结果集

-- 3. inline functionuse AdventureWorks2008goif exists (       select 1       from   sys.objects              where [type] in (N'TF' ,N'IF' ,N'FN')       and name = 'fn_getPerson2'   )    drop function dbo.fn_getPerson2gocreate function dbo.fn_getPerson2(    @EntityID int)returns tableas    return    select BusinessEntityID          ,PersonType          ,FirstName          ,LastName    from   Person.Person           where BusinessEntityID = @EntityIDgoprint('dbo.fn_getPerson2 has been created.')-- select * from dbo.fn_getPerson2(1)

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

-- 3. procedureuse AdventureWorks2008goif exists (       select 1       from   sys.procedures        where name = 'usp_getPerson'   )    drop procedure dbo.usp_getPersongocreate procedure dbo.usp_getPerson(    @EntityID int)asbegin    --....... do some process    -- result of last query will return    select BusinessEntityID          ,PersonType          ,FirstName          ,LastName    from   Person.Person           where BusinessEntityID = @EntityIDendgoprint('dbo.usp_getPerson has been created.')-- exec dbo.usp_getPerson @EntityID = 1

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

阅读全文

0 0