Basic LINQ to SQL Question: How to call stored procedure and retrieve single return value.

Question

I'm new to LINQ and am having a problem getting proper results from a simple (working) stored procedure that takes no parameters and returns a single Integer. When calling this sproc with LINQ its returnvalue is always 0. When run using tableadapter or directly on the sql server it works, returning 6 digit values like 120123.

Here is my LINQ code:

Dim MeetingManager As New MeetingManagerDataContext
Dim MeetingID As Integer = MeetingManager.NewMeetingID().ReturnValue

Here is the NewMeetingID procedure:

    ALTER PROCEDURE [dbo].[NewMeetingID]
AS
SET NOCOUNT ON
BEGIN

    BEGIN TRANSACTION

    SELECT UniqueNumber
    FROM tblTakeANumber
    WHERE NumberID = 1

        BEGIN
            UPDATE tblTakeANumber SET UniqueNumber = UniqueNumber + 1
            WHERE (NumberID = 1)
        END

    COMMIT TRANSACTION

    RETURN
END

Am I missing someting?

Answer 1

Return value is the value that a stored procedure returns, like:

create procedure TestProc()
as 
return 1

But your stored procedure is probably selecting a result, like:

create procedure TestProc()
as 
select 1 as Col1

To retrieve the first column, specify it by name:

Dim Col1 As Integer = TestDataCintext.TestProc().Single().Col1

EDIT: Per your comment, here's how you could modify the stored procedure to return the integer. Note the (updlock) and isolation level:

ALTER PROCEDURE [dbo].[NewMeetingID]
AS
SET NOCOUNT ON
BEGIN
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRANSACTION

    declare @UniqueNumber int

    SELECT  @UniqueNumber = UniqueNumber
    FROM    tblTakeANumber (updlock)
    WHERE   NumberID = 1

    UPDATE  tblTakeANumber 
    SET     UniqueNumber = @UniqueNumber + 1
    WHERE   NumberID = 1

    COMMIT TRANSACTION

    RETURN @UniqueNumber
END