C# ASP.NET - Get last value from a Stored Procedure Parameter

Question

I have a Stored Procedure which inserts different fields. But however there is that when inserted I want to pick and place on the screen. It is entered into the database in the following way:

cmd.Parameters.AddWithValue("@numero", 0);

What’s happening inside the Stored Procedure is as follows::

IF @numero=0
BEGIN
    BEGIN TRANSACTION

    SELECT TOP 1 @ult_nr=nRequesicao FROM cabecalho 
    WHERE eliminado=0
    ORDER BY 1 DESC 

    SET @ult_nr=@ult_nr+1

    INSERT INTO cabecalho(nRequesicao,nomeEmpresa,colaborador,nota,local_entrega)
    VALUES(@ult_nr,@empresa,@empregado,@obs_cab,@local)

    INSERT INTO linhas(nRequesicao,quantidade,descricao,valor,observacoes)
    VALUES(@ult_nr,@qtd,@produto,@valor,@obs_linha)

    SELECT * FROM V_Requisicao
    WHERE nrequesicao=@ult_nr
    ORDER BY id

    COMMIT
END
ELSE
BEGIN
    INSERT INTO linhas(nRequesicao,quantidade,descricao,valor,observacoes)
    VALUES(@numero,@qtd,@produto,@valor,@obs_linha)

    SELECT * FROM V_Requisicao
    WHERE nrequesicao=@numero
    ORDER BY id

END

How can I get the last value and put it on a label?

Answer 1

If what you want is to get a return parameter from your Stored Procedure, you can do this by declaring a parameter of type "output" and recovering that value after execution in C#. See in the example.

The declaration of the AS

create procedure dbo.MinhaSP
    @numero int,
    @retorno int ouptput
as
    // Seu código aqui
    set @retorno = 100

After that in C# you can recover the value as follows:

// seu código...
cmd.Parameters.AddWithValue("@numero", 0);
cmd.Parameters.Add("@retorno", SqlDbType.Int, 4);
cmd.Parameters["@retorno"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
var ret = cmd.Parameters["@retorno"].Value;

Answer 2

Kawaii, if you want the value of @ult_nr, you will need to mark it as an exit parameter. including think it is not necessary, you could use own @numero. Then modify your process to the following:

BEGIN TRANSACTION

IF @numero=0
BEGIN
    SELECT TOP 1 @numero=nRequesicao + 1 FROM cabecalho 
    WHERE eliminado=0
    ORDER BY 1 DESC 

    INSERT INTO cabecalho(nRequesicao,nomeEmpresa,colaborador,nota,local_entrega)
    VALUES(@numero,@empresa,@empregado,@obs_cab,@local)   
END

INSERT INTO linhas(nRequesicao,quantidade,descricao,valor,observacoes)
VALUES(@numero,@qtd,@produto,@valor,@obs_linha)

COMMIT

SELECT * FROM V_Requisicao
WHERE nrequesicao=@numero
ORDER BY id

then you would need to change the direction of your parameter:

var numero = 0;
var param = cmd.Parameters.Add("@numero", SqlDbType.Int, 4);
param.Direction = ParameterDirection.InputOutput;
param.Value = numero;

then after executing your command, just read the parameter value.

cmd.ExecuteNonQuery();
numero = numero.Value as int;