|
P: 9
|
Hi! I've been breaking my head trying to get the output variables from my Stored Procedure.
This is my SP code -
CREATE PROCEDURE GetKitchenOrderDetail(
-
@idService int,
-
-
--outPut Variables
-
@idUser int OUTPUT,
-
@estTimeDelivery datetime = null OUTPUT,
-
@timeDelivered datetime = null OUTPUT,
-
@timeOrder datetime = null OUTPUT,
-
@estTimePrepare int OUTPUT,
-
@estatus int OUTPUT
-
-
)
-
AS
-
BEGIN
-
-
--getting the output variables
-
SELECT @idUser = s.idUser, @estTimeDelivery = ko.estTimeDelivery, @timeDelivered = ko.timeDelivery, @timeOrder = s.timeOrder, @estTimePrepare = (DATEDIFF(MINUTE, @timeOrder, @estTimeDelivery)), @estatus = ko.estatus FROM ServiceOffered AS s JOIN KitchenOrder AS ko ON ko.idService = s.idService JOIN Area AS a on a.idArea = ko.idArea WHERE s.idService = @idService
-
-
--getting the values I want from a query
-
SELECT koi.idItem, koi.price, koi.quantity, mi.name, mi.itemDescription, c.name AS category FROM KitchenOrderItems koi JOIN MenuItem mi ON mi.idItem = koi.idItem JOIN Category as c ON c.idCategory = mi.idCategory WHERE koi.idService = @idService END
And this is my C# Code -
SqlCommand cmd = new SqlCommand("GetKitchenOrderDetail", new SqlConnection(connectionString));
-
cmd.CommandType = System.Data.CommandType.StoredProcedure;
-
cmd.Connection.Open();
-
-
//add serviceId
-
cmd.Parameters.AddWithValue("@idService", idService);
-
//start adding the output parameters //method1 I tried creating a parameter object SqlParameter estTimePrepare = new SqlParameter("@estTimePrepare", System.Data.SqlDbType.Int); -
estTimePrepare.Direction = System.Data.ParameterDirection.Output;
-
-
cmd.Parameters.Add(estTimePrepare);
-
//method2 I tried creating the parameters directly in the command cmd.Parameters.Add("@estTimeDelivery", System.Data.SqlDbType.DateTime); -
cmd.Parameters["@estTimeDelivery"].Direction = System.Data.ParameterDirection.Output;
-
-
cmd.Parameters.Add("@timeDelivered", System.Data.SqlDbType.DateTime);
-
cmd.Parameters["@timeDelivered"].Direction = System.Data.ParameterDirection.Output;
-
-
cmd.Parameters.Add("@timeOrder", System.Data.SqlDbType.DateTime);
-
cmd.Parameters["@timeOrder"].Direction = System.Data.ParameterDirection.Output;
-
-
cmd.Parameters.Add("@estatus", System.Data.SqlDbType.Int);
-
cmd.Parameters["@estatus"].Direction = System.Data.ParameterDirection.Output;
-
-
-
//method3,
-
cmd.Parameters.AddWithValue("@idUser", 0);
-
-
-
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);
If I debug the SP in SQL Management Studio, I get the correct values. But in C#, after executing the reader, whenever I try to access the parameter values (example, cmd.Parameters["@timeOrder"].Value), all output parameters are always 0.
Any ideas?
Thanks!!!
| |
