4

I've created a stored procedure similar to the one below (I'm using this cut down version to try and figure our the problem). 

CREATE PROCEDURE bsp_testStoredProc
AS
BEGIN

CREATE TABLE #tmpFiles  
(
    AuthorName NVARCHAR(50), 
    PercentageHigh INT
) 

-- Insert data into temp table

SELECT AuthorName, PercentageHigh FROM #tmpFiles 
ORDER BY PercentageHigh DESC

DROP TABLE #tmpFiles

RETURN 0
END

From my C# code in VS2008, I'm trying to use the Query component with the Use Existing Stored Procedure option to connect this up to a DataTable / DataGridView to display the results.

However, because I'm selecting from a temporary table, in the Query component properties Visual Studio does not display any columns being returned from the stored procedure. I assume that it has trouble determining the data types being used since the SP is not based on a real schema.

Connecting to different stored procedures that select from real tables do show the columns correctly.

Does anyone know away around this? Is there some sort of hint I can add somewhere to explicitly state what sort of data will be returned?

Thanks in advance.

John Sibly
  • 22,782
  • 7
  • 63
  • 80
  • 1
    for explicit hinting of return types in your SPROC, this solution may work: http://stackoverflow.com/questions/16593473/ef-cant-infer-return-schema-from-stored-procedure-selecting-from-a-temp-table – JoeBrockhaus Dec 19 '14 at 20:54

1 Answers1

1

For info, you might consider using a "table variable" rather than a temporary table (i.e. @FOO rather than #FOO) - this might help a little, and it certainly helps a few tempdb issues.

With temporary tables - no there is no way of explicitly declaring the SPs schema. I would perhaps suggest using a simplified version of the SP while you generate your wrapper classes - i.e. have it do a trivial SELECT of the correct shape.

Alternatively, I would use LINQ to consume a UDF, which does have explicit schema.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Switching to a table variable worked. I am still confused why there should be a difference in the behaviour of temporary tables and table variables though. Thanks, John – John Sibly Oct 15 '08 at 10:25
  • Temp tables are not as tightly bound into the SP schema as table variables, so it is quite possible that the FMTONLY switch simply does a better job with table variables... Glad it helped, though ;-p – Marc Gravell Oct 15 '08 at 10:56
  • Besides - table variables don't cause recompiles etc, so worth switching just for that (assuming you don't have to index your #table) – Marc Gravell Oct 15 '08 at 10:58
  • Great - I'll stick with the table variable then as there is no need to index. Thanks for the advice. – John Sibly Oct 15 '08 at 12:19
  • 1
    Sometimes there are reasons why you need to use a #TempTable instead of @TableVar ... and in those cases, supplying a faux-SELECT at the beginning of your SPROC, as a data contract if you will, should solve that issue. This also affects EF & SSIS data controls. My colleague and I ran into this issue and he figured out the solution described here: http://stackoverflow.com/questions/16593473/ef-cant-infer-return-schema-from-stored-procedure-selecting-from-a-temp-table – JoeBrockhaus Dec 19 '14 at 20:52
  • @Joe agreed; the engine assumes lots of things about table cars, and you can't index – Marc Gravell Dec 20 '14 at 09:53