Home / Questions / 47637
Accepted
Alejandro Ricotti
Asked: 2020-02-04 11:05:14 +0800 CST 2020-02-04 11:05:14 +0800 CST

Get OUTPUT from Stored Procedure

  • 772

I have for example this stored procedure

ALTER PROCEDURE dbo.PersonaDoc
@cDocumento varchar (20)
AS
SELECT Ruta_Line 
FROM Persona
WHERE (documento= @cDocumento)

What difference would it make to consume it in C# using ExecuteNonQuery, ExecuteScalar or ExecuteReader (depending on the case the values ​​you want to return) if the stored procedure is

ALTER PROCEDURE dbo.PersonaDoc
@cDocumento varchar (20)
@resultado int OUTPUT
AS
SELECT SET@resultado = nombre
FROM Persona
WHERE (documento= @cDocumento)

What is not clear to me is the difference between consuming the data that the stored procedure brings directly or using an output value for it, I suppose that in the first case the advantage would be that I can bring several values ​​and with the OUTPUT only one, but assuming i just want a return value is it the same?

c#

1 Answers

  1. Best Answer

    Differences:

    • ExecuteNonQuerywill execute the Stored Procedure and will not return any results
    • ExecuteScalarwill return a single data, with which you can make use of the side of your application
    • ExecuteReaderis used when you want to get a set of data i.e. multiple rows and multiple columns.

    Now, taking as reference the Stored Procedure

    ALTER PROCEDURE dbo.PersonaDoc @cDocumento VARCHAR(20) @resultado INT OUTPUT
AS
    SELECT @resultado = nombre
    FROM Persona
    WHERE (documento = @cDocumento)

    When executing:

    • ExecuteNonQueryyou will not get result
    • ExecuteScalarit will turn you nullaround since in it you SELECTmake an assignment with @resultado = nombre, to return a result you must add SELECT @resultadoto the end of your Stored Procedure
    • ExecuteReaderin the same way it will return you null, you must do the same as the ExecuteScalaror in its case a SELECT * FROM Persona(as an example)

    Update

    With OUTPUTit you make a reference by value to the variable that you have assigned as output value, in the case of your Stored Procedure you could use ExecuteScalaror OUTPUT, both can be adjusted to the way you program. If you are using C#, a code example would be the following to get the value of the document using Direction = ParameterDirection.Output;:

    using(SqlConnection conn = new SqlConnection(ConnectionString ))
    using(SqlCommand cmd = new SqlCommand("PersonaDoc", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@cDocumento", SqlDbType.VarChar, 20).Direction = ParameterDirection.Output;

        conn.Open();
        cmd.ExecuteNonQuery();

        string documentoId = Convert.ToInt32(cmd.Parameters["@cDocumento"].Value);
        conn.Close();
    }
    • 2