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

 

1. 使用 Table Function 返回结果集

 


  2. -- 1. table function
  3. use AdventureWorks2008
  4. go
  5.  
  6. if exists (
  7. select 1
  8. from sys.objects
  9. where [type] in (N'TF' ,N'IF' ,N'FN')
  10. and name = 'fn_getPerson'
  11. )
  12. drop function dbo.fn_getPerson
  13. go
  14.  
  15. create function dbo.fn_getPerson
  16. (
  17. @EntityID int
  18. )
  19. returns @result table (EntityID int ,PersonType varchar(10) ,FirstName varchar(50) ,LastName varchar(50))
  20. as
  21. begin
  22. insert into @result
  23. (
  24. EntityID
  25. ,PersonType
  26. ,FirstName
  27. ,LastName
  28. )
  29. select BusinessEntityID
  30. ,PersonType
  31. ,FirstName
  32. ,LastName
  33. from Person.Person
  34. where BusinessEntityID = @EntityID
  35.  
  36. return -- return must be last sql
  37. end
  38. go
  39.  
  40.  
  41. print('dbo.fn_getPerson has been created.')
  42.  
  43. -- select * from dbo.fn_getPerson(1)
2. 使用 Inline Function 返回结果集

 

 


  2. -- 3. inline function
  3. use AdventureWorks2008
  4. go
  5.  
  6. if exists (
  7. select 1
  8. from sys.objects
  9. where [type] in (N'TF' ,N'IF' ,N'FN')
  10. and name = 'fn_getPerson2'
  11. )
  12. drop function dbo.fn_getPerson2
  13. go
  14.  
  15. create function dbo.fn_getPerson2
  16. (
  17. @EntityID int
  18. )
  19. returns table
  20. as
  21. return
  22. select BusinessEntityID
  23. ,PersonType
  24. ,FirstName
  25. ,LastName
  26. from Person.Person
  27. where BusinessEntityID = @EntityID
  28. go
  29.  
  30. print('dbo.fn_getPerson2 has been created.')
  31.  
  32. -- select * from dbo.fn_getPerson2(1)
3. 使用存储过程返回结果集

 

 


  2. -- 3. procedure
  3. use AdventureWorks2008
  4. go
  5.  
  6. if exists (
  7. select 1
  8. from sys.procedures
  9. where name = 'usp_getPerson'
  10. )
  11. drop procedure dbo.usp_getPerson
  12. go
  13.  
  14. create procedure dbo.usp_getPerson
  15. (
  16. @EntityID int
  17. )
  18. as
  19. begin
  20. --....... do some process
  21.  
  22. -- result of last query will return
  23. select BusinessEntityID
  24. ,PersonType
  25. ,FirstName
  26. ,LastName
  27. from Person.Person
  28. where BusinessEntityID = @EntityID
  29.  
  30. end
  31. go
  32.  
  33.  
  34. print('dbo.usp_getPerson has been created.')
  35.  
  36. -- exec dbo.usp_getPerson @EntityID = 1

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