SQL Server 存储过程返回结果集的几种方式

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. -- 1. table function

2. use AdventureWorks2008

3. go

4.  

5. if exists (

6. select 1

7. from sys.objects

8. where [type] in (N'TF' ,N'IF' ,N'FN')

9. and name = 'fn_getPerson'

10. )

11. drop function dbo.fn_getPerson

12. go

13.  

14. create function dbo.fn_getPerson

15. (

16. @EntityID int

17. )

18. returns @result table (EntityID int ,PersonType varchar(10) ,FirstName varchar(50) ,LastName varchar(50))

19. as

20. begin

21. insert into @result

22. (

23. EntityID

24. ,PersonType

25. ,FirstName

26. ,LastName

27. )

28. select BusinessEntityID

29. ,PersonType

30. ,FirstName

31. ,LastName

32. from Person.Person

33. where BusinessEntityID = @EntityID

34.  

35. return -- return must be last sql

36. end

37. go

38.  
39.  

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

41.  

42. -- select * from dbo.fn_getPerson(1)

2. 使用 Inline Function 返回结果集

 

 

1. -- 3. inline function

2. use AdventureWorks2008

3. go

4.  

5. if exists (

6. select 1

7. from sys.objects

8. where [type] in (N'TF' ,N'IF' ,N'FN')

9. and name = 'fn_getPerson2'

10. )

11. drop function dbo.fn_getPerson2

12. go

13.  

14. create function dbo.fn_getPerson2

15. (

16. @EntityID int

17. )

18. returns table

19. as

20. return

21. select BusinessEntityID

22. ,PersonType

23. ,FirstName

24. ,LastName

25. from Person.Person

26. where BusinessEntityID = @EntityID

27. go

28.  

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

30.  

31. -- select * from dbo.fn_getPerson2(1)

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

 

 

1. -- 3. procedure

2. use AdventureWorks2008

3. go

4.  

5. if exists (

6. select 1

7. from sys.procedures

8. where name = 'usp_getPerson'

9. )

10. drop procedure dbo.usp_getPerson

11. go

12.  

13. create procedure dbo.usp_getPerson

14. (

15. @EntityID int

16. )

17. as

18. begin

19. --....... do some process

20.  

21. -- result of last query will return

22. select BusinessEntityID

23. ,PersonType

24. ,FirstName

25. ,LastName

26. from Person.Person

27. where BusinessEntityID = @EntityID

28.  

29. end

30. go

31.  
32.  

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

34.  

35. -- exec dbo.usp_getPerson @EntityID = 1

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