Ayuda con Procedimiento almacenado para guardar maestro detalle

Question

hola amigos resulta que tengo un SP que guarda el maestro-detalle; pero el problema radica en lo siguiente

en el maestro unicamento debe haber un registro (unique) hasta aqui no hay problema y lo valida pero en el detalle se guardan los registro pese a que en el maestro no.

es decir para validar replico un registro que ya existe en la tabla maestro y en efecto el SP me dice que no se puede insertar el registro XXXX debido a que ya existe; pero en el detalle si inserta los registros podrian ayudarme con esta transaccion por favor gracias.

USE [MCCUR]
GO
/****** Object:  StoredProcedure [dbo].[PGUARDA_OFERTA_ECO_MAESTRO_DETALLE]    Script Date: 14/09/2015 10:09:08 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[PGUARDA_OFERTA_ECO_MAESTRO_DETALLE]
@xml text
as
DECLARE @hDoc int

Begin TRANSACTION

DECLARE @Error int
	EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml
	Declare @IdOferta int
	Insert into OFERTAS (USUARIOID,LICITACIONID,MREQUERIMIENTOID,PROVEEDORID,GPO,GEN,ESP,DIF,VAR,PRECIO_OFERTADO,CANT_MAX,DESCUENTO,PRECIO_NETO)	
	SELECT * FROM OPENXML(@hDoc, N'/ROOT/OfertaEcoTec') with (USUARIOID INT,LICITACIONID int,REQID int, PROVEEDORID int,GPO char(3),GEN char(3),ESP char(4),DIF char(2),[VAR] char(2),PRECIOOFER float,CANTMAXOFER int,DESCUENTOOFER float,PRECIONETO float)
	Select @IdOferta =@@identity
	--almacendo el error  por si no se ejecuta la instruccion correctamente
	
	--si la variable de error es diferente de cero ejecuto el codigo para cancelar la transaccion
	
	   --si el primer inset se realiza con exito pasamos al segundo insert de marcas
		Insert into MARC_OFERT (OFERTAID,MARCA,OFICIO_EXIME,TRAMITE_INCLUSION,ORIGEN,FABRICANTE,RFC_FABRICANTE)		
		SELECT @IdOferta,* FROM OPENXML(@hDoc, N'/ROOT/OfertaEcoTec/MARCASOFER') with (MARCA VARCHAR(200),OFICIO VARCHAR(200), INCLUSION VARCHAR(200), ORIGEN VARCHAR(200),FABRICANTE VARCHAR(200),RFCFAB VARCHAR(30))
		EXEC sp_xml_removedocument @hDoc
	    
		SET @Error=@@ERROR

    if (@Error<>0)  GOTO TratarError
		
		COMMIT TRANSACTION	 
		
TratarError: 
 if @Error <>0 
  BEGIN
	PRINT 'Ocurrio un error durante la transaccion'
	ROLLBACK TRANSACTION
  END

---

Horacio Xochitemol Bautista

Answer 1

Hola Xochitemol,

No, no comparto la filosofía "choque avisa", lo ideal sería que la aplicación lo valide y que en el caso correcto recién ejecute la inserción.

Ahora, si persistes hacerlo en el procedimiento almacenado entonces primero revisa si el valor existe y de acuerdo a eso realiza la inserción, no esperes el error , ¿cuál es el campo que tiene la restricción de unicidad?

IF (NOT EXISTS (SELECT 1 FROM MiTabla WHERE (Campo = Valor)))
BEGIN
    /*El valor no existe, hacer inserciones*/
END

REITERO, la validación debería hacerse en la aplicación con un método que busque la existencia del valor en la tabla, de acuerdo a eso se procede con la inserción caso contrario se informa al usuario del caso, no debería ser el procedimiento el encargado de enviar mensajes a la aplicación.

Lo dejo a tu consideración.

Answer 2

Saludos,

Si tus tablas están relacionadas entonces existe integridad referencial, no podrás grabar nada en el detalle si no existe en el maestro, ahora que versión ocupas de SQL Server? OPENXML no es óptimo, ocupa mucha memoria, si usas SQL Server 2008 o superior puedes usar el tipo de dato XML y usar sus métodos, en mi blog tengo un artículo como hacerlo:

Leer Archivos XML

Lo otro es que especialices tus Stored's, crea un Stored para el maestro y otro para el Detalle y maneja la transación en tu aplicativo con TransactionScope, podrías compartir tu XML y así podamos ver la manera de mejorar tus Stored.

---

Ayacucho - Perú
Recuerda si mi solución atiende tu consulta por favor márcala como útil y como respuesta.

http://litigiouslobo.blogspot.com/
El Blog de Steve Morrison

Answer 3

DECLARE @Error int
	EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml
	Declare @IdOferta int
	Insert into OFERTAS (USUARIOID,LICITACIONID,MREQUERIMIENTOID,PROVEEDORID,GPO,GEN,ESP,DIF,VAR,PRECIO_OFERTADO,CANT_MAX,DESCUENTO,PRECIO_NETO)	
	SELECT * FROM OPENXML(@hDoc, N'/ROOT/OfertaEcoTec') with (USUARIOID INT,LICITACIONID int,REQID int, PROVEEDORID int,GPO char(3),GEN char(3),ESP char(4),DIF char(2),[VAR] char(2),PRECIOOFER float,CANTMAXOFER int,DESCUENTOOFER float,PRECIONETO float)
	Select @IdOferta =@@identity.@error=@@error
	--almacendo el error  por si no se ejecuta la instruccion correctamente
	
	--si la variable de error es diferente de cero ejecuto el codigo para cancelar la transaccion
	
	   --si el primer inset se realiza con exito pasamos al segundo insert de marcas
if @error!=0
begin
		Insert into MARC_OFERT (OFERTAID,MARCA,OFICIO_EXIME,TRAMITE_INCLUSION,ORIGEN,FABRICANTE,RFC_FABRICANTE)		
		SELECT @IdOferta,* FROM OPENXML(@hDoc, N'/ROOT/OfertaEcoTec/MARCASOFER') with (MARCA VARCHAR(200),OFICIO VARCHAR(200), INCLUSION VARCHAR(200), ORIGEN VARCHAR(200),FABRICANTE VARCHAR(200),RFCFAB VARCHAR(30))
		EXEC sp_xml_removedocument @hDoc
end	    
		SET @Error=@@ERROR

tu variable @error solo vale para la ultima ejecución, en cada ejecución puedes ir recogiéndola como hice como ejemplo. creo que así lo arreglas

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA

Answer 4

 el archivo xml realmente lo hago en memoria y despues lo paso como parametro al SP. con el siguiente codigo.

    protected void BtonRegistrarOferta_Click(object sender, EventArgs e)
        {

            MemoryStream memoryStream = new MemoryStream();
            XmlWriterSettings xmlWriterSettings = new XmlWriterSettings();

            // If i use Encodings.UTF8 the BOM will be prepended...

            xmlWriterSettings.Encoding = new UTF8Encoding(false);
            xmlWriterSettings.ConformanceLevel = ConformanceLevel.Document;
            xmlWriterSettings.Indent = true;

            XmlWriter writer = XmlWriter.Create(memoryStream, xmlWriterSettings);

                //convierto los datos a datos reales
                double PRECIO_OFERTADO = Convert.ToDouble(this.txtofertado.Text);
                double DESCUENTO = Convert.ToDouble(this.txtdescuento.Text);
                double IMPORTE_DESC = (PRECIO_OFERTADO * DESCUENTO) / 100;
                double PRECIO_NETO = PRECIO_OFERTADO - IMPORTE_DESC;
                double PRECIO_NETO_FINAL = System.Math.Truncate(PRECIO_NETO * 100) / 100;
            
                int CANTMAX = Convert.ToInt32(this.txtmaxofer.Text);

                //calculo el precio_neto
             
                writer.WriteStartDocument();
                writer.WriteStartElement("ROOT");
                writer.WriteStartElement("OfertaEcoTec");
                writer.WriteAttributeString("USUARIOID", (Convert.ToString(Session["IdUsuario"])));
                writer.WriteAttributeString("LICITACIONID", Convert.ToString(this.ddLicitacion.SelectedValue));
                writer.WriteAttributeString("REQID", Convert.ToString(this.ddclave.SelectedValue));
                writer.WriteAttributeString("PROVEEDORID", Convert.ToString(this.lblProveedorId.Text));               
                writer.WriteAttributeString("GPO", Convert.ToString(this.txtgpo.Text));
                writer.WriteAttributeString("GEN", Convert.ToString(this.txtgen.Text));
                writer.WriteAttributeString("ESP", Convert.ToString(this.txtesp.Text));
                writer.WriteAttributeString("DIF", Convert.ToString(this.txtdif.Text));
                writer.WriteAttributeString("VAR", Convert.ToString(this.txtvar.Text));
                writer.WriteAttributeString("PRECIOOFER",this.txtofertado.Text);
                writer.WriteAttributeString("CANTMAXOFER", Convert.ToString(CANTMAX));              
                writer.WriteAttributeString("DESCUENTOOFER", Convert.ToString(this.txtdescuento.Text));
                writer.WriteAttributeString("PRECIONETO", Convert.ToString(PRECIO_NETO_FINAL));
             
                //foreach (GridViewRow row in this.GridMarcas.Rows)
                foreach (GridViewRow row in GridMarcas.Rows)
                {
                    writer.WriteStartElement("MARCASOFER");


                    Label MARCA = (Label)row.FindControl("lblItemClase");
                    Label OFICIO = (Label)row.FindControl("lblItemOficio");
                    Label INCLUSION = (Label)row.FindControl("lblItemTramite");
                    Label ORIGEN = (Label)row.FindControl("lblOrigen");
                    Label FABRICANTE = (Label)row.FindControl("lblItemFabricante");
                    Label RFCFAB = (Label)row.FindControl("lblItemRfc");                                     

                    writer.WriteAttributeString("MARCA",HttpUtility.HtmlEncode( MARCA.Text).Trim());
                    writer.WriteAttributeString("OFICIO", HttpUtility.HtmlEncode(OFICIO.Text).Trim());
                    writer.WriteAttributeString("INCLUSION", HttpUtility.HtmlEncode(INCLUSION.Text).Trim());
                    writer.WriteAttributeString("ORIGEN", HttpUtility.HtmlEncode(ORIGEN.Text).Trim());
                    writer.WriteAttributeString("FABRICANTE", HttpUtility.HtmlEncode(FABRICANTE.Text).Trim());
                    writer.WriteAttributeString("RFCFAB", HttpUtility.HtmlEncode(RFCFAB.Text).Trim());

                    writer.WriteEndElement();
                }                
                writer.WriteEndElement();                
                writer.Close();

                string result = Encoding.UTF8.GetString(memoryStream.GetBuffer());     

                //conexion a la base de datos
                conexion cn = new conexion();
                // SqlConnection cnx = cn.conectar();

                using (SqlConnection cnx = cn.conectar())
                {
                    using (SqlCommand cmd = new SqlCommand("PGUARDA_OFERTA_ECO_MAESTRO_DETALLE"))
                    {
                        try
                        {//Asignamos la conexión y pasamos el parámetro
                            cmd.Connection = cnx;
                            cmd.Parameters.AddWithValue("@xml", result);
                            cmd.CommandType = CommandType.StoredProcedure;
                            cnx.Open();
                            cmd.ExecuteNonQuery();
                            cnx.Close();
                        }
                        catch (Exception Err)
                        {
                            errorgral = Err.Message.ToString() + " - " + Err.Source.ToString();
                            MessageBoxShow(this, errorgral);
                        }
                    }
                }                      
        }

no entiendo muy bien lo que hace esta linea Select @IdOferta =@@identity.@Error=@@error porque me manda error.

---

Horacio Xochitemol Bautista

Answer 5

Cámbiala de esta manera:

SELECT @IdOferta =@@identity
SET @Error=@@error 

---

Ayacucho - Perú
Recuerda si mi solución atiende tu consulta por favor márcala como útil y como respuesta.

http://litigiouslobo.blogspot.com/
El Blog de Steve Morrison

Answer 6

Hola Xochitemol,

OJO, usar @@identity es una pésima practica, utiliza la función SCOPE_IDENTITY()

SELECT @IdOferta = SCOPE_IDENTITY();
SET @Error = @@error;

Por otro lado, sigo pensando que la mejor estrategia es que valides si el valor existe (aunque no estoy de acuerdo pero incluso puedes hacerlo desde el procedimiento), si no existe el valor entonces puedes hacer la inserción con toda la tranquilidad de no recibir un error.

¿Cuál es el campo que guardas unicidad?

Answer 7

 ESTA ES LA ESTRUCTURA DE LA TABLA 

USE [MCCUR]
GO

/****** Object:  Table [dbo].[OFERTAS]    Script Date: 14/09/2015 05:46:44 p.m. ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[OFERTAS](
	[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[USUARIOID] [int] NULL,
	[LICITACIONID] [int] NOT NULL,
	[MREQUERIMIENTOID] [int] NOT NULL,
	[PROVEEDORID] [int] NOT NULL,
	[GPO] [varchar](3) NULL,
	[GEN] [varchar](3) NOT NULL,
	[ESP] [varchar](4) NOT NULL,
	[DIF] [varchar](2) NOT NULL,
	[VAR] [varchar](2) NOT NULL,
	[PRECIO_OFERTADO] [float] NOT NULL,
	[CANT_MAX] [int] NOT NULL,
	[CANT_MIN] [int] NULL,
	[DESCUENTO] [float] NOT NULL,
	[PRECIO_NETO] [float] NOT NULL,
	[STATUS_MOV_PRECIO] [int] NULL,
	[STATUS_CANT_MEN] [int] NULL,
	[STATUS_DESC_CERO] [int] NULL,
	[STATUS_PAIS] [int] NULL,
	[STATUS_MOV_CLAVE] [int] NULL,
	[STATUS_CLAVE_NOREQ] [int] NULL,
	[STATUS_DESEC_TEC] [int] NULL,
	[STATUS_MAY_MED] [int] NULL,
	[STATUS_MUESTRA] [int] NULL,
	[STATUS_VARIOS] [int] NULL,
	[LUGAR_LIC] [int] NULL,
	[ASIGNACION] [int] NULL,
	[PORC_ASIG] [int] NULL,
	[INSACULACION] [int] NULL,
	[STATUS_TEMP]  AS ((((((((([STATUS_MOV_PRECIO]+[STATUS_CANT_MEN])+[STATUS_DESC_CERO])+[STATUS_PAIS])+[STATUS_MOV_CLAVE])+[STATUS_CLAVE_NOREQ])+[STATUS_DESEC_TEC])+[STATUS_MAY_MED])+[STATUS_MUESTRA])+[STATUS_VARIOS]),
	[OBSERV] [varchar](80) NULL,
 CONSTRAINT [PK_OFERTAS] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_OFERTAS_UNICA] UNIQUE NONCLUSTERED 
(
	[USUARIOID] ASC,
	[LICITACIONID] ASC,
	[MREQUERIMIENTOID] ASC,
	[PROVEEDORID] ASC,
	[GPO] ASC,
	[GEN] ASC,
	[ESP] ASC,
	[PRECIO_OFERTADO] ASC,
	[DESCUENTO] ASC,
	[PRECIO_NETO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[OFERTAS]  WITH CHECK ADD  CONSTRAINT [FK_OFERTAS_Agenda] FOREIGN KEY([LICITACIONID])
REFERENCES [dbo].[AGENDA] ([Id])
GO

ALTER TABLE [dbo].[OFERTAS] CHECK CONSTRAINT [FK_OFERTAS_Agenda]
GO

ALTER TABLE [dbo].[OFERTAS]  WITH CHECK ADD  CONSTRAINT [FK_OFERTAS_Usuarios] FOREIGN KEY([USUARIOID])
REFERENCES [dbo].[USUARIOS] ([IdUsuario])
GO

ALTER TABLE [dbo].[OFERTAS] CHECK CONSTRAINT [FK_OFERTAS_Usuarios]
GO

---

Horacio Xochitemol Bautista

Answer 8

Hola Xochitemol,

¿Es necesario componer el constraint de unicidad con las columnas PRECIO_OFERTADO, DESCUENTO, PRECIO_NETO?

Creo que el diseño de la restricción no es del todo correcto.

Toma la opción de validar antes por la existencia de los campos que definen la unicidad o caso contrario reaccionar sobre el error (no es de mi entero gusto) pero lo que te propone Miguel no debería traerte problemas, el único cambio es en la línea:

Select @IdOferta =@@identity.@error=@@error

por:

SELECT @IdOferta = SCOPE_IDENTITY();
SET @Error = @@error;

Answer 9

Haber amigos hice algunas modificaciones  en el SP, queda de la siguiente manera y funciona solo que no estoy muy convencido, podrian decirme si es correcto; perdon pero apenas estoy empezando con los SP. Gracias

USE [MCCUR]
GO
/****** Object:  StoredProcedure [dbo].[PGUARDA_OFERTA_ECO_MAESTRO_DETALLE]    Script Date: 14/09/2015 10:09:08 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[PGUARDA_OFERTA_ECO_MAESTRO_DETALLE]
@xml text
as
DECLARE @hDoc int
DECLARE @Error int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml
Declare @IdOferta int
Begin TRANSACTION
 BEGIN TRY 
	Insert into OFERTAS (USUARIOID,LICITACIONID,MREQUERIMIENTOID,PROVEEDORID,GPO,GEN,ESP,DIF,VAR,PRECIO_OFERTADO,CANT_MAX,DESCUENTO,PRECIO_NETO)	
	SELECT * FROM OPENXML(@hDoc, N'/ROOT/OfertaEcoTec') with (USUARIOID INT,LICITACIONID int,REQID int, PROVEEDORID int,GPO char(3),GEN char(3),ESP char(4),DIF char(2),[VAR] char(2),PRECIOOFER float,CANTMAXOFER int,DESCUENTOOFER float,PRECIONETO float)
	--RECUPERO DE IDENTIFICADOR DE INSERCCION
	SELECT @IdOferta = SCOPE_IDENTITY();
	--Guardo el numero de Error que genere 
	SET @Error = @@error;

	--Inserto registros en la tabla de detalles de las ofertas recibidas

	Insert into MARC_OFERT (OFERTAID,MARCA,OFICIO_EXIME,TRAMITE_INCLUSION,ORIGEN,FABRICANTE,RFC_FABRICANTE)		
	SELECT @IdOferta,* FROM OPENXML(@hDoc, N'/ROOT/OfertaEcoTec/MARCASOFER') with (MARCA VARCHAR(200),OFICIO VARCHAR(200), INCLUSION VARCHAR(200), ORIGEN VARCHAR(200),FABRICANTE VARCHAR(200),RFCFAB VARCHAR(30))
	EXEC sp_xml_removedocument @hDoc
	    
	SET @Error = @@error;

	COMMIT TRANSACTION
		
 END TRY
 
 BEGIN CATCH
  ROLLBACK TRANSACTION
  PRINT 'Error al Guardar la oferta'
 END CATCH 

---

Horacio Xochitemol Bautista

Answer 10

Hola Xochitemol,

Si ya estas manejando una transacción y el error lo controlas con try..catch entonces no requieres de la variable para capturar el error, con esto es suficiente:

ALTER PROCEDURE [dbo].[PGUARDA_OFERTA_ECO_MAESTRO_DETALLE]
	@xml text
AS
BEGIN
	DECLARE @hDoc int;	
	DECLARE @IdOferta int

	EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml	
	
	BEGIN TRANSACTION
	BEGIN TRY 
		INSERT INTO OFERTAS (USUARIOID,LICITACIONID,MREQUERIMIENTOID,PROVEEDORID,GPO,GEN,ESP,DIF,VAR,PRECIO_OFERTADO,CANT_MAX,DESCUENTO,PRECIO_NETO)	
		SELECT * FROM OPENXML(@hDoc, N'/ROOT/OfertaEcoTec') with (USUARIOID INT,LICITACIONID int,REQID int, PROVEEDORID int,GPO char(3),GEN char(3),ESP char(4),DIF char(2),[VAR] char(2),PRECIOOFER float,CANTMAXOFER int,DESCUENTOOFER float,PRECIONETO float)
		--RECUPERO DE IDENTIFICADOR DE INSERCCION
		SET @IdOferta = SCOPE_IDENTITY();
		
		--Inserto registros en la tabla de detalles de las ofertas recibidas
		INSERT INTO MARC_OFERT (OFERTAID,MARCA,OFICIO_EXIME,TRAMITE_INCLUSION,ORIGEN,FABRICANTE,RFC_FABRICANTE)
		SELECT @IdOferta,* FROM OPENXML(@hDoc, N'/ROOT/OfertaEcoTec/MARCASOFER') WITH (MARCA VARCHAR(200),OFICIO VARCHAR(200), INCLUSION VARCHAR(200), ORIGEN VARCHAR(200),FABRICANTE VARCHAR(200),RFCFAB VARCHAR(30))
		EXEC sp_xml_removedocument @hDoc

		COMMIT TRANSACTION
	END TRY 
	BEGIN CATCH
	  ROLLBACK TRANSACTION
	  PRINT 'Error al Guardar la oferta'
	END CATCH 
END

Answer 11

Ok pues muchas gracias lo utilizare de esa forma saludos.

---

Horacio Xochitemol Bautista

Answer 12

No esteve!!!, ese @@error será del select anterior, en mi mensaje había un error de sintaxis puse un punto y era una coma... eso era todo

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA

Answer 13

No Willams!!!, @@error se resetea en cada instrucción!, eso nunca capturaría un error en el insert anterior

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA

Answer 14

Xochitemol, la respuesta no es "ya lo he solucionado con tu ayuda" sino lo que realmente te ayudó. Lo que te propuso wilams es la solución mas elegante, mucho mas que capturar el error, y esa es la que deben ver los usuarios que vuelvan a este mensaje después.

---

Comparte lo que sepas, aprende lo que no sepas (FGG)
portalSQL
El rincón del DBA