IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'CPJ_Campanas_Generar') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE CPJ_Campanas_Generar 
END
GO

CREATE PROCEDURE CPJ_Campanas_Generar		@pJson VARCHAR(8000)
AS 
SET NOCOUNT ON 

/*Variables recibidas por parámetro a traves del JSON*/
DECLARE @pFlagUsarTrans			TINYINT
DECLARE @pModoDebug				TINYINT
DECLARE @pId_Usuario			SMALLINT
DECLARE @pId_Contacto			INT
DECLARE @pTbl					SMALLINT

SET @pFlagUsarTrans			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'FlagUsarTrans')), 0)
SET @pModoDebug				= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'ModoDebug')), 0)
SET @pId_Usuario			= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJson,'Id_Usuario')), 0)
SET @pId_Contacto			= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJson,'Id_Contacto')), 0)
SET @pTbl					= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJson,'Tbl')), 0)
/*FIN Variables recibidas por parámetro a traves del JSON*/

/*Variables Locales*/
DECLARE @Id_Cliente				INT
DECLARE @Cant_Contactos_Generados INT
/*FIN Variables Locales*/

BEGIN TRY
	IF @pFlagUsarTrans <> 0 
	BEGIN
		BEGIN TRAN
	END

	IF(@pModoDebug = 1)
	BEGIN
		PRINT 'Ingreso a Stored Procedure: CPJ_Campanas_Generar'
		PRINT 'Parámetros: '+ @pJson  
	END
	
	/*===================================================================*/	
	
	SELECT * 
	INTO #CRM_Contactos
	FROM CRM_Contactos
	WHERE
	Id_Contacto = @pId_Contacto

	/*===================================================================*/	

	SET @Cant_Contactos_Generados = 0
	
	--Recorro todos los clientes taggeados por el usuario		
	DECLARE curCliTaggeados CURSOR FOR
	SELECT CONVERT(INT, Val) AS Id_Cliente
	FROM TagFile_
	WHERE
	Usr = @pId_Usuario
	AND Tbl = @pTbl

	OPEN curCliTaggeados
	FETCH NEXT FROM curCliTaggeados INTO @Id_Cliente
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF(@pModoDebug = 1)
		BEGIN
			PRINT 'Id_Cliente: ' + CONVERT(VARCHAR(10), @Id_Cliente)
		END

		UPDATE #CRM_Contactos
		SET
		Id_Contacto = (SELECT MAX(Id_Contacto) + 1 FROM CRM_Contactos),
		Id_Cliente = @Id_Cliente
	
		INSERT INTO CRM_Contactos
		SELECT * FROM #CRM_Contactos
	
		SET @Cant_Contactos_Generados = @Cant_Contactos_Generados + 1

		FETCH NEXT FROM curCliTaggeados INTO @Id_Cliente
	END
	CLOSE curCliTaggeados
	DEALLOCATE curCliTaggeados
	--FIN Recorro todos los clientes taggeados por el usuario

	/*===================================================================*/	

	IF @pFlagUsarTrans <> 0 COMMIT

	SELECT	 0							AS ErrorNumber 
			,0							AS ErrorSeverity  
			,0							AS ErrorState 
			,0							AS ErrorProcedure  
			,@Cant_Contactos_Generados	AS ErrorLine		
			,''							AS ErrorMessage

	IF(@pModoDebug = 1)
	BEGIN
		PRINT 'Fin Stored Procedure: CPJ_Campanas_Generar'
	END
	
END TRY  


BEGIN CATCH  
    EXECUTE usp_GetErrorInfo

	IF @pFlagUsarTrans <> 0  
	BEGIN
		ROLLBACK
	END
	
	--------------------------------------------------------------------------------------------------------
	IF (SELECT CURSOR_STATUS('global','curClientesTaggeados') ) = -1
		DEALLOCATE curClientesTaggeados	
	--------------------------------------------------------------------------------------------------------
	
	RETURN 9999
END CATCH

RETURN 0 

--Ejemplo de uso
--EXEC CPJ_Campanas_Generar '{"FlagUsarTrans":"0", "ModoDebug":"1", "Id_Usuario":"-1", "Id_Contacto":"2", "@pTbl":"400"}'