IF EXISTS (SELECT
						 *
					 FROM
						 sys.objects
					 WHERE
						object_id = OBJECT_ID(N'[dbo].[P_RELATORIO_EST_PROD_PRECO]') AND
						type IN ( N'P', N'PC' ))
	DROP PROCEDURE [dbo].[P_RELATORIO_EST_PROD_PRECO]
GO
CREATE PROCEDURE [P_RELATORIO_EST_PROD_PRECO] (@CDUSU  INT=1,@CD_FILIAL varchar(1000)) AS
	SET NOCOUNT ON
	
declare @cols nvarchar(max);
declare @cols2 nvarchar(max);
declare @stmt nvarchar(max);
CREATE TABLE #TMPFILIAL (cd_emp int, cd_filial INT);
	IF @CD_FILIAL =''
	BEGIN
		INSERT INTO #TmpFilial
		SELECT CD_EMP, CD_FILIAL
		FROM PRC_FILIAL
	end
	ELSE
	begin
		PRINT 'INSERT 3 '+CONVERT(VARCHAR, GETDATE(), 108);
		INSERT INTO #TMPFILIAL
		SELECT CD_EMP,CD_FILIAL
		FROM PRC_FILIAL
		WHERE 
 
			  CD_FILIAL IN(SELECT ID FROM UFD_PRM_IN_STRING(@cd_filial));
      end

select @cols = isnull(@cols + ', ', '') + '[' + CAST(T.CD_FILIAL AS VARCHAR(4)) + ']' from (select distinct CD_FILIAL from #TMPFILIAL) as T
select @cols2 = isnull(@cols2 + ', ', '') + 'isnull ( [' + CAST(T.CD_FILIAL AS VARCHAR(4)) + '],0) as '+'[' +CAST(T.CD_FILIAL AS VARCHAR(4)) +']' from (select distinct CD_FILIAL from #TMPFILIAL) as T
--select @cols2
select @stmt = '
declare @usu int = '+ convert(varchar,1) + 
'SELECT CD_PROD,DS_PROD,
' +@cols2+
'
FROM
(
     SELECT A.CD_PROD,
     B.DS_PROD,
     A.CD_FILIAL,
     A.VLR_TABELA
FROM EST_PROD_PRECO A
     INNER JOIN EST_PROD B ON A.CD_EMP = B.CD_EMP
                             AND A.CD_PROD = B.CD_PROD
           INNER JOIN GLB_ARV_MERC_FILTROS FILTROS
                       ON A.CD_EMP = FILTROS.CD_EMP
                       AND FILTROS.CD_USU = @USU
                       AND A.CD_PROD = FILTROS.CD_PROD
           WHERE FILTROS.CD_EMP = 1   
) AS SourceTable PIVOT(AVG(VLR_TABELA) FOR CD_FILIAL IN(' + @cols + ')
     ) as P'
--select @stmt
exec sp_executesql  @stmt = @stmt;

 