SQL Server stored procedure output parameter and return data from select

entity-framework-core output sql-server stored-procedures

Question

I have a stored procedure in SQL Server 2012:

CREATE PROCEDURE GetImmediateManager   
   @managerID INT OUTPUT  
AS  
BEGIN  
   SET @managerID = 6;

   SELECT * 
   FROM Roles;
END  

When I remove select * from Roles; the output value (@managerID) is returned correctly to my C# code. But when the procedure has select * .., the value returned by output parameter is null.

How can I return select and output at the same time?

In C# my code looks like this:

dbContext.Database.OpenConnection();

DbCommand cmd = dbContext.Database.GetDbConnection().CreateCommand();
cmd.CommandTimeout = 15;
cmd.CommandText = "GetImmediateManager";
cmd.CommandType = System.Data.CommandType.StoredProcedure;

var rowsCountParam = new SqlParameter("@managerID", System.Data.SqlDbType.Int);
rowsCountParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(rowsCountParam);

using (var reader = cmd.ExecuteReader())
{
    tasks = reader.MapToList<TaskManagerTask>();
    //rowsCount = (int)rowsCountParam.Value;
}
1
0
1/25/2018 6:08:41 PM

Accepted Answer

The simplistic answer is to add @managerID to your select statement

For a less simplistic perhaps the following, I changed the names a bit to reflect the use, get parameter after it closes.

var managerIDParam = new SqlParameter("@managerID", System.Data.SqlDbType.Int);
managerIDParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(managerIDParam);

using (var reader = cmd.ExecuteReader())
{
    tasks = reader.MapToList<TaskManagerTask>();
}
int managerIDParamUsed = (int)managerIDParam.Value;
0
1/25/2018 3:40:40 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow