Get OUTPUT of Stored Procedure

Question

Get OUTPUT of Stored Procedure

Navigation

#1 by (2 votes)

0

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 there be to consuming 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 it would be advantageous to bring several values and with the OUTPUT one alone, but assuming that I only want a return value is the same?

sql c# sql-server

asked by Alejandro Ricotti 03.02.2017 в 20:05

source

1 answer

Perform join on a table depending on the value of a field Installer when downloading MYSQL [closed]

score 2 · Accepted Answer

Differences:

ExecuteNonQuery will execute the Stored Procedure and will not return any results
ExecuteScalar will revolverá a single data, with which you can make use of the side of your application
ExecuteReader is used when you want to obtain a data set, that is, 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:

ExecuteNonQuery will not get results
ExecuteScalar will revolve null given that in SELECT you make an assignment with @resultado = nombre , to return a result you must add SELECT @resultado at the end of your Stored Procedure
ExecuteReader in the same way will return null , you must do the same as the ExecuteScalar or in your case a SELECT * FROM Persona (by way of example)

Update

With the OUTPUT 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 ExecuteScalar or, OUTPUT , both can be adjusted to the way you program. If you use C #, a code example would be the following to obtain 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();
    }