IDENT_CURRENT RETURN NULL-CodePudding

I am writing a stored procedure in SQL Server that inserts and should show the id of the inserted record but when I use @@IDENTITY, SCOPE_IDENTITY() or IDENT_CURRENT it returns null.

Table structure:

CREATE SEQUENCE Idfac
    START WITH 2000
    INCREMENT BY 1;
GO

CREATE TABLE Factura
(
    idFactura [int] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR Idfac),
    Nombre_sucursal [varchar](20) NOT NULL,
    Nombre_cliente [varchar](100) NOT NULL,
    Numero_factura [varchar](10) NOT NULL,
    Fecha Datetime,
    Subtotal DECIMAL(18, 2) ,
    IGV AS CAST(Subtotal * 0.18 AS decimal(18,2)),
    Total DECIMAL(18, 2) ,

    CONSTRAINT chk_Subtotal 
        CHECK(Subtotal IS NULL OR Subtotal <> 0),
    CONSTRAINT chk_total 
        CHECK (Total IS NULL OR Total <> 0) 
);

Stored procedure:

CREATE PROCEDURE USP_RegistrarFactura
    @Nombre_sucursal varchar(20),
    @Nombre_cliente varchar(100),
    @Numero_factura varchar(10),
    @Fecha Datetime,
    @Subtotal DECIMAL(18, 2) 
AS
BEGIN  
    INSERT INTO Factura (Nombre_sucursal, Nombre_cliente, Numero_factura, Fecha, Subtotal, total)
    VALUES (@Nombre_sucursal, @Nombre_cliente, @Numero_factura, @Fecha, @Subtotal, @Subtotal * 0.18   @Subtotal)

    SELECT IDENT_CURRENT('Factura') AS Result;
END

CodePudding user response：

Since you're using a SEQUENCE for the default of the IdFactura column, you're not going to be able to use @@IDENTITY, SCOPE_IDENTITY() or IDENT_CURRENT. Instead, you're going to need to use OUTPUT clause for the INSERT command.

I changed your procedure to match what you're trying to achieve.:

CREATE PROCEDURE USP_RegistrarFactura
    @Nombre_sucursal varchar(20),
    @Nombre_cliente varchar(100),
    @Numero_factura varchar(10),
    @Fecha Datetime,
    @Subtotal DECIMAL(18, 2)
AS
BEGIN  
    DECLARE @ReturnTable TABLE (
        [Return] INT);
    INSERT INTO Factura (Nombre_sucursal, Nombre_cliente, Numero_factura, Fecha, Subtotal, total)
    OUTPUT Inserted.idFactura INTO @ReturnTable
    VALUES (@Nombre_sucursal, @Nombre_cliente, @Numero_factura, @Fecha, @Subtotal, @Subtotal * 0.18   @Subtotal)

    RETURN(SELECT [Return] FROM @ReturnTable)
END

Using your sample:

DECLARE @return_value INT
EXEC @return_value = [dbo].[USP_RegistrarFactura]
     @Nombre_sucursal = N'La Molina',
     @Nombre_cliente = N'Mario',
     @Numero_factura = N'001230036',
     @Fecha = N'2021-11-10',
     @Subtotal = 150

SELECT 'Return Value' = @Return_value

The result as follows:

PS: It's important to note that you're trying to use the returned value of the procedure. Take a look at the documentation for Returning Data Using a Return Code.