Linq to SQL question: How to retrieve return value and resultset from Stored Proc

Question

Folks,

I have a stored proc as following:

create procedure p1 as
begin
    declare @n1 int
    select 
       @n1=count(*)
    from
       Employee

    select top 100
        *
    from
        Employee
    return @n1
end

I want to capture @n1 as well as the Employee resultset using Linq To SQL in my C# code.

I would appreciate any help.

Thanks.

Ash

Answer 1

You need an OUTPUT parameter

create procedure p1(@n1 int =0 OUTPUT ) as
begin
    select 
       @n1=count(*)
    from
       Employee

    select top 100
        *
    from
        Employee
end

--And remember, you have to indicate a parameter is for output when you execute the proc

Declare @val int
exec p1 @val OUTPUT

Answer 2

SOLVED:

//I can access the resultset as following:
var emps = dataContext1.p1;

//I can get to @n1 by this:
int i1=int(emps).ReturnValue;