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

Navigation
1

I have a Stored Procedure that inserts different fields. But nevertheless there is that when being inserted I want to catch and put on the screen. It is inserted into the database in the following way: 

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

It is not inserting 0 here, what is 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 in a Label?

    
asked by anonymous 05.05.2016 / 17:54

2 answers

1

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

The declaration of SP 

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

After that in C # you can retrieve 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;
    
05.05.2016 / 19:52
0

Kawaii, if you want the value of @ult_nr , you will have to mark it as an output parameter. even think it is not necessary, you could use @numero itself. Then modify your procedure 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, you would just read the value of the parameter. 

cmd.ExecuteNonQuery();
numero = numero.Value as int;
    
05.05.2016 / 19:51
How to verify the domain that the user is how to use SUM within an ORDER BY (mysql) [closed]