Database.SqlQuery calling stored procedure that has multiple output parameters

Question

I have a stored procedure like below which takes 1 input parameter ( Name ) and returns 2 output parameters( EmployeeId and Salary). Our stored procedure will insert Name into Employee table and give us back EmployeeId and Salary.

CREATE PROCEDURE dbo.insertemployee
@iName varchar(500),
@OEmployeeId int OUTPUT,  
@OSalary Money OUTPUT

We are using EF Code First approach. I am able to insert records into employee table and cannot find how I can access my two output parameters. I know that I need to use like below. Can anybody tell me what must be Result. According to MSDN, it can be a class that has column names as properties. But my case is that we are not returning columns of my table but we are using two output parameters and I need know how to access those two output parameters @OEmployeeId and @OSalary.

context.Database.SqlQuery<Result>(" exec dbo.insertemployee....);

public class Result
{
   // what properties I must give here       
}

Peter Hansen · Accepted Answer · 2016-08-25 16:16:52Z

32

The method you are trying to use only works for results of a query. It can't put the values of the output parameters into a new object for you automatically.

You have to create the parameters explicitly and read their values after the stored procedure has been run.

So if you have a stored procedure like this:

CREATE PROCEDURE dbo.insertemployee
(
    @iName varchar(500),
    @OEmployeeId int OUTPUT,  
    @OSalary Money OUTPUT
)
AS
BEGIN
    SELECT @OEmployeeId = 1337;
    SELECT @OSalary = 1000;
END

... you can execute it and get the results of the parameters like this:

using (var ctx = new Context())
{
    var nameParam = new SqlParameter("iName", "TestName");

    var employeeIdParam = new SqlParameter("OEmployeeId", SqlDbType.Int) 
    { 
        Direction = System.Data.ParameterDirection.Output 
    };

    var salaryParam = new SqlParameter("OSalary", SqlDbType.Money) 
    { 
        Direction = System.Data.ParameterDirection.Output 
    };

    ctx.Database.ExecuteSqlCommand(
        "insertemployee @iName, @OEmployeeId out, @OSalary out", 
        nameParam, employeeIdParam, salaryParam);

    var employeeId = (int)employeeIdParam.Value;
    var salary = (decimal)salaryParam.Value;
}

edited Aug 25, 2016 at 16:16

answered Feb 20, 2014 at 21:15

Peter Hansen

8,9271 gold badge39 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ziggler Over a year ago

Thank you very much Peter Hansen. This solved my issue. It was really hard for me to find out any posts and blogs on issue of calling a stored procedure that has output parameters using EF Code First.

Johna Over a year ago

A big thanks Peter Hanson, I was trying to do this but I was forgetting the "out" inside the query, thank you very much!

Collectives™ on Stack Overflow

Database.SqlQuery calling stored procedure that has multiple output parameters

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related