Cannot get a stored procedure table output into my .NET Application

Question

I have a stored procedure that looks like this:

DECLARE @A TABLE ( TabYear int, Std decimal(18,2))
DECLARE @B TABLE ( TabYear int, Std decimal(18,2))

BEGIN 
    DECLARE @FinalTable TABLE (TabYear int, HoldayA decimal(18,2), HolidayB decimal(18,2))

BEGIN
    INSERT INTO @A(TabYear, std)
            Select ... from ...
    INSERT INTO @B(TabYear, Std)
            Select ... from ...
END

INSERT INTO @FinalTable(TabYear, HoldayA, HoldayB) 
    SELECT 
        A.TabYear, a.Std, U.Std 
    FROM
        @A A 
    LEFT JOIN 
        @B U ON a.TabYear = U.TabYear 
END

Select * from @FinalTable

Now I want to get that into a DATASET within my .NET application using the TableAdapter Wizard. I choose :"Use existing stored procedure" and choose the procedure (above) but as Data Column I get only Column1. It looks like the DataTableAdapter does not recognize the table. If I let the SP run on the server everything is fine. I get the table as I wish. I also checked if the wizard recognizes other SP's I am working with on the table and that works fine. But I have to admit that all the other SP's I use are straigth Select commands nothing with querying @Tables like in this SP. Could anyone help me get the table into my application. I do not have go go with the DataSet necessarily a code solution getting the data into an array or something would help me too.

`It looks like the DataTableAdapter does not recognize the table.` simple, use ***actual DB calls and functions, NOT a wizard***. Also looking at your procedure, that can be cleaned up a bit. I see some issues having a good execution plan and performance issues. For one: ***both table's (@A, @B) should have an identity column*** and they **dont**. — Trevor, Mar 30 '16 at 14:04

score 3 · Accepted Answer · answered Mar 30 '16 at 13:58

3

You cannot achieve this using the TableAdapter Wizard because your stored procedure has no defined output because its dynamically created. You must manually construct the TableAdapter in your code.

answered Mar 30 '16 at 13:58

PhillipH

6,182
1
15
25

I created an adapter according to this solution (http://stackoverflow.com/questions/13402003/how-to-populate-a-datatable-from-a-stored-procedure) and it works. thanks! – ruedi Mar 30 '16 at 15:23

score 2 · Answer 2 · answered Mar 30 '16 at 14:00

Probably because you SELECT * from a table that's defined within the sproc, so the analyzer can't determine the schema. Either define the columns outside of the wizard or change your sproc to select specific columns (although the wizard won't be able to determine the type, so you'll still have to edit the data table schema).

You could also turn the sproc into one SELECT that uses subqueries instead of filling a table variable, but that may have been done for performance reasons...

I do not have go go with the DataSet necessarily

That's fine, but you'll still hove to map the columns of the result set to a structure in C# code at some point.

Wizards only take you so far; at some point you need to start casting your own spells...

Cannot get a stored procedure table output into my .NET Application

2 Answers2