Mr.Ken Mr.Ken - 2 months ago 13
ASP.NET (C#) Question

Get values multi output parameter (stored procedure ) in MVC

My stored procedure :

CREATE PROCEDURE [dbo].[GetProduct]
@ProdId int output
@ProdCode nvarchar(20) output
AS
BEGIN
SET @ProdId = 1
SET @ProdCode = 'PROD00001'
END


When I call this stored procedure in my action, how to get values of the 2 output parameters
@prodId
and
@ProdCode
?

Answer

put select command in your sp as

CREATE PROCEDURE [dbo].[GetProduct]
@ProdId int output
@ProdCode nvarchar(20) output
as
begin
    set @ProdId=1
    set @ProdCode='PROD00001'
    SELECT @ProdId, @ProdCode
end

and call this in action like

   SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=yourDbName;Integrated Security=True;Pooling=False");
            con.Open();
            SqlCommand cmd = new SqlCommand("GetProduct", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter ProdId = new SqlParameter("@ProdId", SqlDbType.Int);
            ProdId.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(ProdId);
            SqlParameter ProdCode = new SqlParameter("@ProdCode", SqlDbType.NVarChar,20);
            ProdCode.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(ProdCode);

            cmd.ExecuteNonQuery();
            string ProductCode = (string)cmd.Parameters["@ProdCode"].Value;
            int ProductId = Convert.ToInt32(cmd.Parameters["@ProdId"].Value);
Comments