How to retrieve return value and resultset from Stored Proc using Linq To SQL?

Consider a stored procedure like this:

create procedure p1 as
begin
    declare @n1 int;
 开发者_运维百科   select  @n1=count(*)
    from  Employee;

    select top 100 *
    from   Employee;
    return @n1;
end

How can I capture @n1 as well as the Employee resultset using Linq To SQL in C#?

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

SOLVED:

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

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