Stored Procedure returning 0 on scope identity

Question

I'm using this same procedure on an old project and it has been working for a while, so i can't figure out what is wrong with this one and why it returns 0 when I insert.

IF OBJECT_ID('dbo.spInsertArtigo') IS NOT NULL
    DROP PROCEDURE spInsertArtigo
GO

CREATE PROCEDURE spInsertArtigo
    @IdUser INT,
    ----- hArtigos -----
    @MotivoCriacao NVARCHAR(100) = NULL,
    @CodeRequest TINYINT,
    @Cliente TINYINT,
    @ReferenciaCliente NVARCHAR(50),
    @ReferenciaInterna NVARCHAR(7),
    @Indice NVARCHAR(10),
    @Projecto NVARCHAR(50),
    @Desenho NVARCHAR(50),
    @CadenciaMensal INT,
    @NumCOMDEV NVARCHAR(50),
    @QTDLancamentoFormas INT,
    @CapacidadeReal NVARCHAR(20) = NULL,
    @Observacoes NVARCHAR(100) = NULL,
    ----- hCaixa -----
    @TipoCaixa NVARCHAR(50) = NULL,
    @QTDPecasCaixa INT = NULL,
    @UnidadeProducao NVARCHAR(50) = NULL,
    @CelulaProducao NVARCHAR(50) = NULL,
    @Peso FLOAT,
    @NumKanbansProducao INT = NULL,
    ----- hMetodologiaProducao
    @TipoMetodologia nchar(3) = NULL,
    @QTDMetodoProducao INT = NULL,

    @ID INT = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    BEGIN
        INSERT INTO hArtigos (MotivoCriacao, CodeRequest, Cliente, ReferenciaCliente, ReferenciaInterna, Indice, Projecto, Desenho,
                              CadenciaMensal, NumCOMDEV, QTDLancamentoFormas, CapacidadeReal, Observacoes, IdUser)
        VALUES (@MotivoCriacao, @CodeRequest, @Cliente, @ReferenciaCliente, @ReferenciaInterna, @Indice, @Projecto, @Desenho,
                @CadenciaMensal, @NumCOMDEV, @QTDLancamentoFormas, @CapacidadeReal, @Observacoes, @IdUser)

        INSERT INTO hCaixa (TipoCaixa, QTDPecasCaixa, UnidadeProducao, CelulaProducao, Peso, NumKanbansProducao, IdArtigo)
        VALUES (@TipoCaixa, @QTDPecasCaixa, @UnidadeProducao, @CelulaProducao, @Peso, @NumKanbansProducao, SCOPE_IDENTITY())

        INSERT INTO hMetodologiaProducao(TipoMetodologia, QTDMetodoProducao, IdArtigo) 
        VALUES (@TipoMetodologia, @QTDMetodoProducao, SCOPE_IDENTITY())

        INSERT INTO hData(DataCriacao, DataAlteracao, IdArtigo)
        VALUES (GETDATE(), GETDATE(), SCOPE_IDENTITY())

        SELECT @ID = SCOPE_IDENTITY()
    END
END

This is where I call the procedure

public static INT InsertRequisicao(Requisicao req)
{
        string constr = ConfigurationManager.ConnectionStrings["GestaoRequisicoes_ConnectionString"].ConnectionString;
        string query = "spInsertArtigo";

        try
        {
            using (SqlConnection con = new SqlConnection(constr))
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@IdUser", HttpContext.Current.User.Identity.Name);
                cmd.Parameters.AddWithValue("@MotivoCriacao", req.MotivoCriacao);
                cmd.Parameters.AddWithValue("@CodeRequest", req.CodeRequest);
                cmd.Parameters.AddWithValue("@Cliente", req.Cliente);
                cmd.Parameters.AddWithValue("@ReferenciaCliente", req.ReferenciaCliente);
                cmd.Parameters.AddWithValue("@ReferenciaInterna", req.ReferenciaInterna);
                cmd.Parameters.AddWithValue("@Indice", req.Indice);
                cmd.Parameters.AddWithValue("@Projecto", req.Projecto);
                cmd.Parameters.AddWithValue("@Desenho", req.Desenho);
                cmd.Parameters.AddWithValue("@CadenciaMensal", req.CadenciaMensal);
                cmd.Parameters.AddWithValue("@NumCOMDEV", req.NumCOMDEV);
                cmd.Parameters.AddWithValue("@QTDLancamentoFormas", req.NumLancamentoFormas);
                cmd.Parameters.AddWithValue("@CapacidadeReal", req.CapacidadeReal);
                cmd.Parameters.AddWithValue("@TipoCaixa", req.TipoCaixa);
                cmd.Parameters.AddWithValue("@QTDPecasCaixa", req.NumPecasCaixa);
                cmd.Parameters.AddWithValue("@UnidadeProducao", req.UnidadeProducao);
                cmd.Parameters.AddWithValue("@CelulaProducao", req.CelulaProducao);
                cmd.Parameters.AddWithValue("@Peso", req.Peso);                 
                cmd.Parameters.AddWithValue("@NumKanbansProducao", req.NumKanbansProducao);
                cmd.Parameters.AddWithValue("@TipoMetodologia", req.TipoMetodologia);
                cmd.Parameters.AddWithValue("@QTDMetodoProducao", req.NumMetodoProducao);
                cmd.Parameters.AddWithValue("@Observacoes", req.Observacoes);

                con.Open();
                return Convert.ToInt32(cmd.ExecuteScalar());
            }

        }
        catch (SqlException ex)
        {
            throw ex;
        }
}

I think everything looks ok... Procedure is exactly as the same as the other and i don't think there's anything wrong on C# code

Does your table have an identity column? Also, why bother with a try/catch if all your going to do is rethrow the error? I would just remove the try/catch block or do something with it. — Sean Lange, Jan 07 '19 at 19:27
yes sql table is fine. i tried adding values manually on db and worked, problem is the executescalar returns 0. And try catch i leave so in future i can log the errors with elmah and show message to user — Jackal, Jan 07 '19 at 19:29
Ohh I think I understand your question now. You are thinking it is going to return the identity value? A stored procedure returns an int as a status of the execution. Your procedure actually does not have a return statement at all so it will always return 0. If you want it to return something else you need to use a return statement or use an output parameter. — Sean Lange, Jan 07 '19 at 19:30
I think you're forgetting your OUTPUT parameter in your C# code. Take a look at [this answer](https://stackoverflow.com/a/10908586/685760) to a very similar question — Mr Moose, Jan 07 '19 at 19:32
yes this explains the 0. I hardly use output on parameters and never noticed this — Jackal, Jan 07 '19 at 19:33

score 4 · Accepted Answer · answered Jan 07 '19 at 19:31

4

Instead of getting the RETURN value of ExecuteScalar(), you need to get the value of the output parameter @ID

answered Jan 07 '19 at 19:31

Tab Alleman

31,483
7
36
52

Ah i see, i didn't even notice this detail because i'm used to just do select at the end of a procedure and run scalar – Jackal Jan 07 '19 at 19:32

Stored Procedure returning 0 on scope identity

1 Answers1