| No Duplicar Info en Stored Procedure al Insertar
HOLA QUE TAL, BUEN DÍA, TENGO UN STORED PROCEDURE QUE LO QUE HACE ES INSERTAR EN TABLAS LA INFO A UN TABLA LLAMADA "TAPSA" LA CUAL ESTA COMPUESTA POR TRES CAMPOS: PLN_ID, CABECERA, LINEA.
Al llenarse la unica linea deiferente es el campo "linea", Los demas se repiten como puedo conseguuir que no se duplique esto? aqui esta el SP.
USE AGOCH_DESA
GO
If EXISTS (SELECT ID from dbo.sysobjects where id = object_id(N'[dbo].[PAPsa]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].PAPsa
GO
CREATE PROCEDURE PAPsa
(
@PLNID INTEGER
)
AS
SET NOCOUNT ON
DECLARE @PLANOGRAM VARCHAR(4000),
@SEGMENT VARCHAR(4000),
@FIXTURE VARCHAR(4000),
@vcMensaje VARCHAR(255),
@MAXY VARCHAR(255)
BEGIN TRY
---------------------|
-- C A B E C E R A --|
---------------------|
INSERT INTO TAPsa
SELECT ''+@PLNID+'','CABECERA','PROSPACE SCHEMATIC FILE'
INSERT INTO TAPsa
SELECT ''+@PLNID+'','CABECERA','; Version 2003.1.0'
INSERT INTO TAPsa
SELECT ''+@PLNID+'','CABECERA',
'Project,'+SUBSTRING((SELECT PLANOGRAM FROM dbo.AGO_CAT_ACN_PLANOGRAMS WHERE PLN_ID=@PLNID
),1,25)+'.psa'+
','+' '+','+'0'+','
-----------------------------------|
-- L I N E A - P L A N O G R A M --|
-----------------------------------|
INSERT INTO dbo.TAPsa
SELECT ''+@PLNID+'','PLANOGRAM','Planogram'
+','+A.PLANOGRAM+','+' '+','
+CAST (B.BASE_WIDTH AS VARCHAR)+','
+CAST (B.HEIGHT AS VARCHAR)+','
+CAST(B.DEPTH AS VARCHAR)+','
+CAST (B.COLOUR AS VARCHAR)+','
+CAST (B.BACK_DEPTH AS VARCHAR)+','+'1'+','
+CAST (B.WIDTH AS VARCHAR)+','
+CAST(B.BASE_HEIGHT AS VARCHAR)+','
+CAST(B.BASE_DEPTH AS VARCHAR)+'1'+','
+CASE WHEN CAST(B.COLOUR AS VARCHAR)='0' THEN '16777215' ELSE CAST(B.COLOUR AS VARCHAR) END+','+'1'+','+' '+','+'0'+','+'1'+','
+CASE WHEN CAST(B.COLOUR AS VARCHAR)='0' THEN '16777215' ELSE CAST(B.COLOUR AS VARCHAR) END+','+'1'+','+'0'+','+'0'+','
+CAST (B.BASE_DEPTH AS VARCHAR)
+','+'0'+','+' '+','+A.PLANOGRAM+','+A.PLANOGRAM
FROM AGO_CAT_ACN_PLANOGRAMS A WITH (NOLOCK)
INNER JOIN AGO_CAT_ACN_SECTION B WITH (NOLOCK)
ON A.PLN_ID = B.PLN_ID
WHERE A.PLN_ID=@PLNID
------------------------------|
--L I N E A - S E G M E N T --|
------------------------------|
INSERT INTO TAPsa
SELECT ''+@PLNID+'','SEGMENT','Segment'+','+' '+','+CAST(A.HEIGHT AS VARCHAR)
+','+'0'+','+' '+','+' '+','+'1'+','+' '
FROM AGO_CAT_ACN_FIXEL A WITH (NOLOCK)
INNER JOIN AGO_CAT_ACN_SECTION B WITH (NOLOCK)
ON
B.PLN_ID=A.PLN_ID AND
A.FRAME_ELEMENT=2
AND A.OCCUPANCY=2
AND A.PLN_ID= @PLNID
ORDER BY
A.X DESC
-----------------------------|
--L I N E A - F I X T U R E--|
-----------------------------|
INSERT INTO TAPsa
SELECT ''+@PLNID+'','FIXTURE','Fixture'+','+CASE WHEN CAST(A.OCCUPANCY AS VARCHAR)=0 THEN '0' ELSE '6' END+','
+CAST(A.FIXEL_ID AS VARCHAR)+','+' '+','
+CAST(A.X AS VARCHAR)+','
+CAST(A.WIDTH AS VARCHAR)+','
+CAST(A.Y AS VARCHAR)+','
+CAST(A.HEIGHT AS VARCHAR)+','
+CAST(A.Z AS VARCHAR)+','
+CAST(A.DEPTH AS VARCHAR)+','+'0'+','+'0'+','+'0'+','
+CAST(A.COLOUR AS VARCHAR)+','+' '
+','+'0'+','+'0'+','+'0'+','+'0'+','+'0'+','
+CAST(A.FILL_COLOR AS VARCHAR)+','+'0'+','+'0'+','
+CASE WHEN CAST(A.MAX_MERCH AS VARCHAR)='0' THEN '30'+','+'1'+','+'1' ELSE CAST(A.MAX_MERCH AS VARCHAR)+','+'1'+','+'1' END +','
+CAST(A.DRAW AS VARCHAR)+','+'0'+','+'0'+','+'0'+','+'0'+','+'0'+' ,'+'0'+','+'0'+','+'0'+','+'0'+','+'1'+','+'0'+',' +'0'+','+'0'+','+'0'+','+'0'+','+'0'+','+' '+','+' '+','+' '+','+'-1'+','+'-1'+','+'-1'+','+'1'+','+'1'+','+'1'+','+'0'+','+'-1'+','+'-1'+','+'1'+','+'-1'+','+'-1'+','+'2'+','+'1'+','+'1'+','+'0'+','+'-1'+','+'-1'+','+'-1'+','+'-1'+','+'-1'+','+'2'+','+'3'+','+'1'+','+'1'+','
+ISNULL(CAST(A.COLOURISCLEAR AS VARCHAR), ' ')+','+' '+','+' '+','+' '+','+' '+','+' '+','+' '+','+' '+','+' '+','+' '
FROM
AGO_CAT_ACN_FIXEL A WITH (NOLOCK)
WHERE PLN_ID=@PLNID
AND OCCUPANCY IN (0,1) AND FRAME_ELEMENT=0
END TRY
BEGIN CATCH
SET @vcMensaje = ERROR_MESSAGE()
RAISERROR (@vcMensaje, 16, 1)
END CATCH
SET NOCOUNT OFF |