Using LinqtoSql, how can I return values from a temp table in a SPROC?

Question

I have a stored procedure that returns values from a temp table. In my DBML, it displays (None) for the return type. What is the trick to get it to recognize the columns from my temp table?

CREATE PROCEDURE [dbo].[GetCategoryPriceRanges] 
@CategoryId int 
AS
BEGIN

DECLARE @MinPrice money, @MaxPrice money 
SELECT  @MinPrice = MIN(ourPrice),@MaxPrice = MAX(ourPrice)

DECLARE @loopCatch int

--catch infinite loops
SELECT @loopCatch = 1
WHILE @thisLow <= @maxPrice AND @loopCatch < 100
 BEGIN
  INSERT INTO #prices(lowRange, hiRange) VALUES (@thisLow, @thisHigh)
  SET @thisLow = @thisHigh + 1
  SET @thisHigh = 2 * @thisLow - 1
  SELECT @loopCatch = @loopCatch + 1
 END
SELECT * FROM #prices
DROP TABLE #prices
END

Answer 1

If you have a table which returns the same set of columns you can assign the stored procedure to return that type in the data model diagram. (Either by dragging the stored prcoedure onto it, or by setting it in the properties of the stored procedure.)

Alternatively you can create a view with this set of columns, and assign the stored procedure to return that. (On the return type property of the stored procedure.)

Answer 2

Not sure about older versions of visual studio, but I'm using 2010 and I resolved the issue by going into the dbml designer and adding a Class.

Then in the new Class, add the the fields from the temp table that the stored proc will return. i.e. right click on the Class and Add -> Property.

Then set the stored proc's Return Type to your newly created Class.

Answer 3

i solved this by adding SET FMTONLY OFF at the top of the stored procedures containing the temp tables. visual studio promptly auto generated the types.