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, Jul 17 '17 at 15:36
Correct, how do I get the result set? It should work as expected with a `Select From`, no? — Luke Danger Kozorosky, Jul 17 '17 at 15:37
do you need a `set nocount on` at the top of your stored-proc? — DaveShaw, Jul 17 '17 at 15:37
You need to Import the stored procedure as a Function. Have a look here https://stackoverflow.com/questions/32140774/getting-data-from-stored-procedure-with-entity-framework/32359095#32359095 — Salah Akbari, Jul 17 '17 at 15:37
Where's the code for the `uspGetSecCapacityNew` stored procedure? You just showed us a script, not the actual stored procedure which is important. — RBarryYoung, Jul 17 '17 at 15:39
@DaveShaw I have set no count before, and have re-added it to my code. When I do that it returns -1 — Luke Danger Kozorosky, Jul 17 '17 at 15:49
@LukeDangerKozorosky Based on that you said *it returns -1* I'm now sure that your problem is like the question I linked, exactly. And your problem will solve if you import it as a function like that I described in the linked answer. — Salah Akbari, Jul 17 '17 at 15:54
@S.Akbari yes, we've been looking into your answer. The problem now for us is that we don't really have a model, like yours had `Products`, we would need an entity named `Capacity`. Is there a way to set up that function without having a model to pass it to? We saw there was an option for Scalar, but that would just return one result, correct? — Luke Danger Kozorosky, Jul 17 '17 at 15:56
@LukeDangerKozorosky Check this when the return value of the procedure has to be Scalar https://stackoverflow.com/questions/31709284/stored-procedure-return-1-for-all-cases-in-entity-framwork/31784986#31784986 — Salah Akbari, Jul 17 '17 at 15:58
@S.Akbari when I try to make the function and click `Get Column Information` it's telling me my stored procedure returns no columns. Along with that when I run the code I get reference errors. Nevermind the Scalar comment, I misunderstood what that meant. — Luke Danger Kozorosky, Jul 17 '17 at 16:08

score 0 · Answer 1 · answered Jul 17 '17 at 17:12

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 '17 at 17:12

djangojazz

14,131
10
56
94

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. – Luke Danger Kozorosky Jul 17 '17 at 17:34
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. – djangojazz Jul 17 '17 at 18:23
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 – Luke Danger Kozorosky Jul 17 '17 at 18:49
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. – djangojazz Jul 17 '17 at 20:22

score 0 · Answer 2 · answered Jul 17 '17 at 20:08

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]);
    }

Stored Procedure is returning number of rows instead of column values

2 Answers2