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
– Trevor, Commented Mar 30, 2016 at 14:04

PhillipH · Accepted Answer · 2016-03-30 13:58:55Z

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, 2016 at 13:58

PhillipH

6,2221 gold badge17 silver badges25 bronze badges

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

1 Comment

ruedi Over a year ago

I created an adapter according to this solution (stackoverflow.com/questions/13402003/…) and it works. thanks!

D Stanley · Accepted Answer · 2016-03-30 14:00:45Z

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...

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related