go 
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
			WHERE ROUTINE_NAME= 'P_GET_NEXT_KEY_TABLE_UPDATE_KEY'
				AND ROUTINE_TYPE='PROCEDURE')
BEGIN
	DROP PROCEDURE P_GET_NEXT_KEY_TABLE_UPDATE_KEY
END
GO

--P_GET_NEXT_KEY_TABLE_UPDATE_KEY 'PDV_VD','CD_VD', 'CD_EMP','CD_FILIAL'
--P_GET_NEXT_KEY_TABLE_UPDATE_KEY 'EST_ORC', 'CD_ORC','CD_EMP','CD_FILIAL'

CREATE PROCEDURE P_GET_NEXT_KEY_TABLE_UPDATE_KEY (@TableName		varchar(100), 
													@CampoChave varchar(100),
													@CD_CHAVE1	varchar(100)=NULL,
													@CD_CHAVE2	varchar(100)=NULL,
													@CD_CHAVE3	varchar(100)=NULL,
													@CD_CHAVE4	varchar(100)=NULL,
													@CD_CHAVE5	varchar(100)=NULL)
as
begin
	set nocount on;

-------------------------------------------------------------
-- A T U A L I Z A C A O GLB_CTR_CHAVE_TBL
-------------------------------------------------------------

Declare @Sql as varchar(8000);


if @CampoChave ='1' OR @CD_CHAVE1 IS NULL
begin
	
	declare @Cmd varchar(500)
	DECLARE @Coluna varchar(150) ;

	Set @Cmd = 'P_GET_NEXT_KEY_TABLE_UPDATE_KEY ''' + @TableName + '''' ;
				
	declare cTmp CURSOR FAST_FORWARD FOR
	SELECT  
		column_name AS COLUNA  
	FROM 
		information_schema.key_column_usage
	WHERE 
		OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
		and table_name= @TableName 
	ORDER BY ORDINAL_POSITION DESC;

 	OPEN cTmp
	FETCH NEXT FROM cTmp INTO @Coluna
	WHILE @@FETCH_STATUS = 0
		BEGIN			 
			Set @Cmd = @Cmd + ', ''' +  @Coluna +''''

			FETCH NEXT FROM cTmp INTO @Coluna
		END
	CLOSE cTmp;
	DEALLOCATE cTmp; 
	Print 'Verifique as posicoes de chave  ' 
	Print @Cmd 
	return
end
 
Declare @SqlFilial as varchar(500);
DELETE  glb_ctr_chave_tbl WHERE TABELA = @TableName

IF @TableName ='PDV_VD'	OR @TableName ='EST_ORC'
BEGIN
--Optimizado com cd_emp =1
Set @Sql =
	'DECLARE @cd_chave int ;						
	declare c_Chave CURSOR FAST_FORWARD FOR
	SELECT CD_FILIAL  
		FROM PRC_FILIAL 
		ORDER BY CD_FILIAL
	OPEN c_Chave
	FETCH NEXT FROM c_Chave INTO @cd_chave
	WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO glb_ctr_chave_tbl(TABELA,CD_CHAVE1,CD_CHAVE2,CD_CHAVE3,CD_CHAVE4,CD_CHAVE5)
			SELECT 
				''' + @TableName + ''' TABELA,
				cd_emp CD_CHAVE1,
				cd_filial CD_CHAVE2,
				MAX('+@CampoChave+') +1  CD_CHAVE3,
				null CD_CHAVE4,
				null CD_CHAVE5
			FROM ' + @TableName + '
			WHERE
				CD_EMP=1
				and cd_filial = @cd_chave
			GROUP BY cd_emp ,cd_filial
			FETCH NEXT FROM c_Chave INTO @cd_chave
		END
	CLOSE c_Chave;
	DEALLOCATE c_Chave;' 
END
ELSE
BEGIN
	SEt @SqlFilial =''
	if (@CD_CHAVE2 ='CD_FILIAL' or @CD_CHAVE3 ='CD_FILIAL' or
			@CD_CHAVE4 ='CD_FILIAL' OR 	@CD_CHAVE5 ='CD_FILIAL')
	BEGIN
		set @SqlFilial = ' Where Cd_Filial = @cd_chave ' 
	END

	Set @Sql = 'INSERT INTO glb_ctr_chave_tbl(TABELA,CD_CHAVE1,CD_CHAVE2,CD_CHAVE3,CD_CHAVE4,CD_CHAVE5)
	SELECT 
		''' + @TableName + ''' TABELA,'

	if @CD_CHAVE2 is null
	begin
		Set @Sql +=  @CD_CHAVE1 + ' CD_CHAVE1,
			MAX('+@CampoChave+') +1 CD_CHAVE2,
			Null CD_CHAVE3,null CD_CHAVE4, null CD_CHAVE5
		FROM ' + @TableName + @SqlFilial + ' GROUP BY 
			' + @CD_CHAVE1  
	end
	Else if @CD_CHAVE3 is null
	begin
		Set @Sql +=  @CD_CHAVE1 + ' CD_CHAVE1, ' + @CD_CHAVE2 + ' CD_CHAVE2,
			MAX('+@CampoChave+') +1 CD_CHAVE3, null CD_CHAVE4, null CD_CHAVE5
			FROM ' + @TableName + @SqlFilial + ' GROUP BY 
			' + @CD_CHAVE1  + ',' + @CD_CHAVE2 
	end
	Else if @CD_CHAVE4 is null
	begin
		Set @Sql +=  @CD_CHAVE1 + ' CD_CHAVE1, ' + @CD_CHAVE2 + ' CD_CHAVE2,' + @CD_CHAVE3 + ' CD_CHAVE3,
			MAX('+@CampoChave+') +1 CD_CHAVE4, null CD_CHAVE5 FROM ' + @TableName + @SqlFilial + ' GROUP BY 
			' + @CD_CHAVE1  + ',' + @CD_CHAVE2 + ',' + @CD_CHAVE3 
	end
	Else if @CD_CHAVE5 is null
	begin
		Set @Sql +=  @CD_CHAVE1 + ', ' + @CD_CHAVE2 + ',' + @CD_CHAVE3 + ',' + @CD_CHAVE4 + ',
			MAX('+@CampoChave+') +1 CD_CHAVE5 FROM ' + @TableName + @SqlFilial + ' GROUP BY 
			' + @CD_CHAVE1  + ',' + @CD_CHAVE2 + ',' + @CD_CHAVE3+ ',' + @CD_CHAVE4
	end

	IF not @sqlfilial =''
	begin
		Set @Sql =
		'DECLARE @cd_chave int ;						
		declare c_Chave CURSOR FAST_FORWARD FOR
		SELECT CD_FILIAL  
			FROM PRC_FILIAL 
			ORDER BY CD_FILIAL
		OPEN c_Chave
		FETCH NEXT FROM c_Chave INTO @cd_chave
		WHILE @@FETCH_STATUS = 0
			BEGIN '
				 + @Sql + ' 
				FETCH NEXT FROM c_Chave INTO @cd_chave
			END
		CLOSE c_Chave;
		DEALLOCATE c_Chave;' 
	END

END

	Exec (@Sql) ;

	SELECT * FROM glb_ctr_chave_tbl
	WHERE @TableName=TABELA
	return;
end
-------------------------------------------------------------
-------------------------------------------------------------