SQL Server 返回结果集的几种方式
2017年12月18日 21:52:24 xxc1605629895 阅读数 7033更多
分类专栏： sqlserver
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xxc1605629895/article/details/78837527
最近用到了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 只返回最后一条查询的结果集