0

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;
}
CDspace
  • 2,639
  • 18
  • 30
  • 36
Robert
  • 2,571
  • 10
  • 63
  • 95
  • Why dont you return `@managerID` with table like `SELECT *, @managerID managerID FROM Roles;` – uzi Jan 25 '18 at 14:58
  • 5
    [When you use a DataReader object, you must close it or read to the end of the data before you can view the output parameters](https://support.microsoft.com/en-gb/help/308621/output-parameters-are-not-returned-when-you-run-an-ado-net-command-in?wa=wsignin1.0) – Alex K. Jan 25 '18 at 14:58
  • This isn't EF Core, it's plain ADO.NET. Output parameters are returned *after* the reader finishes. What does `MapToList` do? Does it read to the end? – Panagiotis Kanavos Jan 25 '18 at 14:59
  • @uzi why do you assume there's a problem with output parameters? How would you read *two* result sets? – Panagiotis Kanavos Jan 25 '18 at 14:59
  • Is this a duplicate here: https://stackoverflow.com/q/10905782/125981 – Mark Schultheiss Jan 25 '18 at 15:12

1 Answers1

0

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;
Mark Schultheiss
  • 32,614
  • 12
  • 69
  • 100