Stored Procedure is returning number of rows instead of column values

Question

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.

Here's how I have my SQL set up:

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:

The ASP call:

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

And the ASP output:

capacity 261

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

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

The stored procedure return is the number of rows, not the result set. What is db here? Is this Entity Framework? — DavidG
– DavidG, Commented Jul 17, 2017 at 15:36
Correct, how do I get the result set? It should work as expected with a Select From, no? — Luke Danger Kozorosky
– Luke Danger Kozorosky, Commented Jul 17, 2017 at 15:37
do you need a set nocount on at the top of your stored-proc? — DaveShaw
– DaveShaw, Commented Jul 17, 2017 at 15:37
You need to Import the stored procedure as a Function. Have a look here stackoverflow.com/questions/32140774/… — Salah Akbari
– Salah Akbari, Commented Jul 17, 2017 at 15:37

djangojazz · Accepted Answer · 2017-07-17 17:12:10Z

0

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:

Dynamic SQL
Temporary storage in the tempdb (# or ## tables)

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.

answered Jul 17, 2017 at 17:12

djangojazz

13.2k10 gold badges64 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Luke Danger Kozorosky Over a year ago

Well we are using a temp table because we need to isolate one column at a time. uspGetEventKillSheetDetailedReport has 5-6 columns in it that we don't need, so our plan was to query uspGetEventKillSheetDetailedReport for the column that we need, store it into a temp table, then query the temp table since we can't query a stored procedure.

djangojazz Over a year ago

You can keep data in memory to query later or else use a permanent table if you are going to do after market analysis. I would not be storing data in the tempdb that you are going to come back to later. It generally is for temporary storage, hence the name. If you really want to do a return from a temporary object yet want to query that object itself later, why not create a permanent table and label it similar to: 'stagingKillSheetDetailedReport'? You are going to get into trouble with analysis of tempdb objects. That is a level generally reserved for SQL Server, not .NET access.

Luke Danger Kozorosky Over a year ago

I wanted to use temp because if we used a permanent db, everytime someone ran the program it would generate 261 more entries to the table (and I'm doing this for 3 queries) so it would get really intense on memory. We found a way to do it instead by using something like from d in ...... select new {d.capacity}. I'll post an answer shortly

djangojazz Over a year ago

Well you are dropping the table every time this procedure is ran. So it stands to reason it is being dropped however x many of times you call it and you can have y results at z times. I just was stating that tempdb is for temporary. You could easily just run a merge statement or other conditional logic if you are afraid of repeat inserts into an area for getting data later.

Luke Danger Kozorosky · Accepted Answer · 2017-07-17 20:08:56Z

I was able to solve it by picking apart my Stored Procedure from ASP instead of trying to import specific data from 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]);
    }

Collectives™ on Stack Overflow

Stored Procedure is returning number of rows instead of column values

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related