title: Dynamic sql in table valued functions
body: <p>I am trying to create a function that returns a table with the result of a substring, but this script must be dynamic where the name of the table must be passed through parameters as well as the join and where parameters.</p>
  <p>Here is a sample of the script that does not work:</p>
  <pre class="lang-sql prettyprint-override"><code>ALTER FUNCTION XXXX.XXXXXXX 
  (
      @pIntegracion int, 
      @PatameterName varchar(max),
      @pJsonProyecto NVARCHAR(50),
      @pField VARCHAR(50),
      @CondicionJoin varchar (max),
      @CondicionWhere varchar (max),
      @pUuid NVARCHAR (50),
      @pTable VARCHAR(max)
  
  )
  RETURNS @Result TABLE (
      Uuid varchar(MAX)
     ,IdProyecto varchar(max) 
     ,Field__COR varchar(max)
  )
  AS
  BEGIN
      INSERT INTO @Result 
      SELECT DISTINCT P.Uuid,@pJsonProyecto,Substring(COALESCE(@pField,''), 0, Charindex('','', COALESCE(@pField,''))) AS Field_COR
      FROM @pTable P
      LEFT JOIN XXXXX A 
      @CondicionJoin
      INNER JOIN XXXXXX D
      ON (P.iddocumento = D.IdDocumento)
      @CondicionWhere
      AND D.Uuid = @pUuid
      AND D.ProcesoMetada = @pIntegracion
      AND P.EstadoConsultaApiAriba = 'Successful_File_Upload'
      AND (COALESCE(@pField,'') LIKE '%,%' OR COALESCE(@pField,'') IS NULL)
  
      RETURN 
  END
  </code></pre>