存储过程返回行数而不是列值

I'm calling a stored procedure from ASP.NET to grab some data for storage in an array. The problem is that in order to return the values I need, I first must call another stored procedure and dump the results into a temp table. This generates the correct records on the SQL side, but when I call it in ASP it returns the index of the last record as an int. So even though SQL generates the correct results, ASP cannot access them when calling upon the stored procedure.

我正在从ASP.NET调用存储过程来获取一些数据以便存储在数组中。问题是，为了返回我需要的值，我首先必须调用另一个存储过程并将结果转储到临时表中。这会在SQL端生成正确的记录，但是当我在ASP中调用它时，它会将最后一条记录的索引作为int返回。因此，即使SQL生成正确的结果，ASP在调用存储过程时也无法访问它们。

Here's how I have my SQL set up:

以下是我设置SQL的方法：

IF OBJECT_ID('#temp') IS NOT NULL
    BEGIN
        DROP TABLE #temp
    END

CREATE TABLE #temp 
(
EventID nvarchar(50),
RunDate date,
SectionCode nvarchar(50),
SectionMapCode nvarchar(50),
DispSort int,
Capacity nvarchar(50),
Attendance int,
PctCap int,
HeatColor nvarchar(50)
)

DECLARE @runDate date = GETDATE()
--Insert results from killsheet sproc into temp table
INSERT #temp  Exec GameDayReporting.dbo.uspGetEventKillSheetDetailedReport @EventID, @runDate;

select Capacity from #temp;

The SQL output:

SQL输出：

The ASP call:

ASP电话：

string option = TempData["option"].ToString();
var secCapacity = db.uspGetSecCapacityNew(option);
ViewData["Capacity"] = secCapacity;
System.Diagnostics.Debug.WriteLine("capacity " + secCapacity);

And the ASP output:

和ASP输出：

capacity 261

容量261

Notice how secCapacity is equal to 261, which is the last row number in the SQL result.

注意secCapacity如何等于261，这是SQL结果中的最后一个行号。

So how do I access the actual query results rather than the size of the data?

那么如何访问实际的查询结果而不是数据的大小？

2 个解决方案

#1

No offense but it seems like you are adding a layer of complexity that does not need to exist. This section:

没有冒犯，但似乎你正在添加一层不需要存在的复杂性。本节：

DECLARE @runDate date = GETDATE()
--Insert results from killsheet sproc into temp table
INSERT #temp  Exec GameDayReporting.dbo.uspGetEventKillSheetDetailedReport @EventID, @runDate;

select Capacity from #temp;

You are inserting into a temporary object in SQL Server with a single proc. Why not just return that dependent proc? Is it a very memory intensive memory operation or something that requires a temporary storage? If not I would just get the result set. Juggling temp storage with returning result sets can be problematic. Especially when you deal with a layer of .NET dealing with returns that are either:

您正在使用单个proc插入SQL Server中的临时对象。为什么不直接返回那个依赖的proc？它是一个内存密集型的内存操作还是需要临时存储的东西？如果不是，我会得到结果集。使用返回结果集来处理临时存储可能会有问题。特别是当你处理一层处理返回的.NET时：

Dynamic SQL
动态SQL
Temporary storage in the tempdb (# or ## tables)
tempdb中的临时存储（＃或##表）

Unless you have some stringent rules I would just do your return data with

除非你有一些严格的规则，否则我会用你的返回数据

GameDayReporting.dbo.uspGetEventKillSheetDetailedReport @EventID, @runDate

Wrap that a List Of Poco objects if you are using Entity Framework or a DataTable if ADO.NET.

如果使用实体框架，则包装Poco对象列表，如果使用ADO.NET，则包装DataTable。

#2

I was able to solve it by picking apart my Stored Procedure from ASP instead of trying to import specific data from SQL.

我能够通过从ASP中分离我的存储过程而不是尝试从SQL导入特定数据来解决它。

    var uspCapacity = from d in db.uspGetEventKillSheetDetailedReport(option, date)
                      select new
                      {
                          d.Capacity
                      };
    var uspAttendance = from d in db.uspGetEventKillSheetDetailedReport(option, date)
                        select new
                        {
                            d.Attendance
                        };
    var uspSectionMapCode = from d in db.uspGetEventKillSheetDetailedReport(option, date)
                        select new
                        {
                            d.SectionMapCode
                        };

    var uspCapacityList = uspCapacity.ToArray();
    var uspAttendanceList = uspAttendance.ToArray();
    var uspSectionMapCodeList = uspSectionMapCode.ToArray();

I tested by using:

我通过使用测试：

    for (var i = 0; i < 3; i++)
    {
        System.Diagnostics.Debug.WriteLine("Capacity " + uspCapacityList[i] + " Attendance " + uspAttendanceList[i] + " Section Map Code " + uspSectionMapCodeList[i]);
    }

#1