KEEP IDENTITY IN MY DATABASE

0

I am developing a management project for a toy store and I have had problems when implementing transactions and it is that when I make a bad insertion of a stored procedure (by the way) my transaction does not display anything ... So I thought about using TRY - CATCH and when I execute the lines and I try my stored procedure in SQL - SERVER the identity is not altered and everything works without problems ... But when consuming my stored procedure of External way (Java FX) is when it does not insert, but if it alters the identity of my table ...

Here's my stored procedure in SQL-SERVER

USE [Jugueteria]
GO
CREATE PROCEDURE [dbo].[pa_insertarMarca]
    @Nombre varchar(50),
    @IdMarcaGenerado int out
AS
BEGIN
    BEGIN TRY
    INSERT INTO Marca (Nombre) VALUES (@Nombre)
    SET  @IdMarcaGenerado = SCOPE_IDENTITY()
    END TRY
    BEGIN CATCH

   --@IdActual guarda el valor previo a una mala insercion--
    DECLARE @IdActual INT = @IdMarcaGenerado-1;
   --Restaura el valor anterior en caso de una mala insercion--
    DBCC CHECKIDENT (Marca, RESEED, @IdActual);
   --Muestra mensaje de error y Numero de error--
    SELECT ERROR_NUMBER() AS Numero_Error,ERROR_MESSAGE() AS Mensaje;
    END CATCH
   --Finalmente se muestra el identity actual (despues del try-catch)--
    DBCC CHECKIDENT (Marca)
END

I hope you can help me improve the stored procedure ... Since what I want is not to lose the identity in my tables ...

Thank you very much for your time.

    
asked by Antonio Labra 05.11.2017 в 05:49
source

0 answers