CODEWITHSUNDEEP
linq-to-sqlstored-procedures
ScottG
ScottG

Reputation: 11121

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

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

Upvotes: 3

Views: 1463

Answers (3)

brooks
brooks

Reputation: 9

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.

Upvotes: 0

Brian Wells
Brian Wells

Reputation: 1582

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.

Upvotes: 0

Bravax
Bravax

Reputation: 10493

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

Upvotes: 2

Related Questions