IF OBJECT_ID( 'DBO.F_LISTAEMTABELA'  ) IS NOT NULL
   DROP FUNCTION DBO.F_LISTAEMTABELA
GO


CREATE FUNCTION DBO.F_LISTAEMTABELA (@LIST VARCHAR(MAX))
   RETURNS @TBL TABLE (NUMBER INT NOT NULL) 
   
AS

BEGIN

/*
FUNÇÃO QUE RETORNA O PARAMENTRO DE LISTA DE INTEIROS EM TABELA PARA CRIACAO DE TEMP OU JOIN

ENTRADA: '1,2,3,4,5'

SAIDA: TABELA
NUMBER
1
2
3
4
5

EXEMPLO:
SELECT * FROM DBO.F_LISTAEMTABELA( '1,2,3')
*/


   DECLARE @POS        INT,
           @NEXTPOS    INT,
           @VALUELEN   INT

   SELECT @POS = 0, @NEXTPOS = 1

   WHILE @NEXTPOS > 0
   BEGIN
      SELECT @NEXTPOS = CHARINDEX(',', @LIST, @POS + 1)
      SELECT @VALUELEN = CASE WHEN @NEXTPOS > 0
                              THEN @NEXTPOS
                              ELSE LEN(@LIST) + 1
                         END - @POS - 1
      INSERT @TBL (NUMBER)
         VALUES (CONVERT(INT, SUBSTRING(@LIST, @POS + 1, @VALUELEN)))
      SELECT @POS = @NEXTPOS
   END
  RETURN
END


GO


/* FIM */