Inserting data with a proc in SQL Server

Question

I need to use a procedure that adds data to the table Pedido and then on the table ItemPedido automatically. But I’m not able to create a Procedure work.

Follow my code below:

create procedure [dbo].[add_pedido] 
                             (@codcli int,
                              @quant varchar(50),
                              @data date,
                              @codprod int,
                              @codpedido int)
as insert into Pedido (CodCli, Data_pedido) 
   values (@codcli, @data)
   insert into ItemPedido (Quant_pedido, CodProduto, CodPedido) 
   values (@quant, @codprod, @codpedido)

The Request data is entered in the database, but the data for Itempedido is not inserted. To execute, I use the following code:

EXEC add_pedido 
    @quant = 2, @data = '23/02/2020', @codprod = 4, @codcli = 5, @codpedido = @@IDENTITY;

And I need Itempedido to inherit Codpedido, but I can’t.

Answer 1

What happens is that the first insertion occurs successfully. However, in the second, an error occurs because the value of @codprod is NULL.

This is caused by the place where the @@IDENTITY.

This function returns the last identity value (identity) after some operation (insertion, in this case).

Your code doesn’t work because you run @@IDENTITY before having inserted anything.

In the documentation you can find more details about the **@@IDENTITY**.

You can also prevent data from being entered into a table instead of another using a transaction. This will make the operation atomic and thus the operation as a whole will only be successful if all operations work.

The version below works as expected.

ALTER PROCEDURE Add_Pedido (@codcli INT,
                              @quant VARCHAR(50),
                              @data DATE,
                              @codprod INT)
AS
    BEGIN TRAN
    INSERT INTO Pedido (CodCli, Data_pedido) 
    VALUES (@codcli, @data);

    INSERT INTO ItemPedido (Quant_pedido, CodProduto, CodPedido) 
    VALUES (@quant, @codprod, @@IDENTITY)
    COMMIT TRAN;

^{See working on Sqlfiddle}