如何选择 [temp table1] = [subselect 1], [temp table2] = [subselect 2] FROM [Stored Procedure]

问题描述

我有一个返回两个选择的存储过程，我在报告中使用了它.第一个选择是以表格格式显示的数据，第二个是要在报表头中显示的元数据，如下所示:

I have a stored procedure that returns two selects, which I use in a report.The first select is data to display in tabular format and the second are metadata to display in the report head, like showed below:

CREATE PROCEDURE dbo. GetReport
@Input INT
AS
BEGIN
   --Get #Metadata
   -- #Results = f(#Metadata) … compex calculation
   SELECT * FROM #Results
   SELECT * FROM #Metadata
END

由于 sproc 计算非常密集，我想为一些最常用的 sproc 参数准备报告行作为纯数据(在两个表中:PrecalcResults 和 PrecalcMetadata).泡沫我会直接选择预先计算的vaues或根据参数用sproc计算它们.

As the sproc calculation is quite intensive, I would like to prepare the report lines as plain data (in two tables: PrecalcResults and PrecalcMetadata) for some mostly used sproc parameters overnight.Lather I would directly select the precalculated vaues or calculate them with the sproc according to the parameters.

出于维护原因，我想使用相同的 sproc 来计算以下数据:1.报告中显示2. 存放在 PrecalcResults 和 PrecalcMetadata 中(带有使用的参数)

For maintenance reasons I would like to use the same sproc to calculate data that would be:1. showed in the report2. be stored in PrecalcResults and PrecalcMetadata (with the used parameters)

如果我有单选 sproc，我会在这里描述一种方法:将存储过程的结果插入临时表

If I would have single select sproc I would an approach desctibed here:Insert results of a stored procedure into a temporary table

由于我有多选 sproc，我想做类似上面的事情，但有两个表.在 .net 中，我会执行 DataSet.Tables[0] 和 DataSet.Tables[1]...，但我想在 tsql 中执行它，以便在日常工作中运行它.

As I have multiselect sproc I would like to do something like above but with two tables.In .net I would do DataSet.Tables[0] and DataSet.Tables[1]..., but I want to do it in tsql, to run it in daily job.

这在 MS SQL 中是否可行?

Is this even possible in MS SQL?

我必须向自己道歉，从下面的答案中可以看出我不是很清楚.我想将此功能实现为纯 TSQL.

I have to apologize myself, from the answer below I can see I was not very clear.I would like to do implement this functionality as pure TSQL.

推荐答案

是的，这是可能的.

正如您所建议的那样从单个存储过程返回多个结果集非常好.

您唯一的潜在问题是 TableAdapter 能够从存储过程中提取两个结果集的限制，但是对于这个问题有一个非常简单的解决方法.

Your only potential issue is the limitation of a TableAdapter being able to pull both result sets from the stored procedure, but there's a very simple work-around for that issue.

这篇关于如何选择 [temp table1] = [subselect 1], [temp table2] = [subselect 2] FROM [Stored Procedure]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！