sql-server - 我可以根据全新 proc 中的 Column 对多个存储过程进行分组吗？避免重复结果？

Question

目标是通过按 Id 对它们进行分组来从这三个存储过程中得到一个结果，以避免重复！

我有三个存储过程，它们根据我创建的逻辑返回具有不同结果的相同表。我被困在如何过滤结果以避免重复，并返回一个包含所有结果的表（基于 Id Col 过滤）。

ALTER  PROC [dbo].[Recommendation]
          @SurveyInstanceId INT

AS
/*
        DECLARE
            @SurveyInstanceId INT = 74

        EXECUTE [dbo].[recommendation]
                  @SurveyInstanceId 
*/


EXECUTE [dbo].[Resources_Question10Answers]
@SurveyInstanceId 

EXECUTE dbo.Resources_Question11Answers
@SurveyInstanceId

EXECUTE [dbo].[Resources_Question1Answers] 
@SurveyInstanceId

标签： sql-serverstored-proceduresgrouping

Answer 1

您可以将存储过程的结果插入到临时表或表变量中，然后将查询结果从该表发送到客户端。

例如：

ALTER  PROC [dbo].[Recommendation]
          @SurveyInstanceId INT

AS
/*
        DECLARE
            @SurveyInstanceId INT = 74

        EXECUTE [dbo].[recommendation]
                  @SurveyInstanceId 
*/
begin
  set nocount on;
  declare @results table (id int, ...)

  insert into @results
  EXECUTE [dbo].[Resources_Question10Answers]
  @SurveyInstanceId 

  insert into @results
  EXECUTE dbo.Resources_Question11Answers
  @SurveyInstanceId

  insert into @results
  EXECUTE [dbo].[Resources_Question1Answers] 
  @SurveyInstanceId

  select id, ...
  from @results 
  group by id
end