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.