FUNÇÕES: Listas de valores numéricos

Boa noite pessoal!

Estive pensando sobre algumas formas de ajudar vocês em necessidades do dia-dia, assim tive a idéia de nas próximas semanas trazer uma relação de queries que facilitam estas necessidades. Uma que eu sempre me deparo é gerar listas numéricas, assim elaborei este artigo para ajuda-los a nesta situação.

Então para uma lista de valores numéricos e tenho as seguintes alternativas:

1. Criar tabela temporária e inserir os registros um a um dentro de um WHILE? Inserir os registros um a um não será tão perfomático, mas se o fizer, não posso esquecer do NOCOUNT, para evitar as mensagens de ‘linhas afetadas’:

SET NOCOUNT ON;

2. Fazer uma consulta com vários UNIONs? Mas quantas linhas mesmo eu vou ter que digitar? Algo em torno de duas linhas para cada valor numérico? Para listas grandes… Nem pensar…

--Criando uma lista de 0-9 sem recursividade.
SELECT 0 AS ID
UNION ALL
  SELECT 1
UNION ALL
  SELECT 2
UNION ALL
  SELECT 3
UNION ALL
  SELECT 4
UNION ALL
  SELECT 5
UNION ALL
  SELECT 6
UNION ALL
  SELECT 7
UNION ALL
  SELECT 8
UNION ALL
  SELECT 9

3. Fazer uma CTE recursiva? Pode ser, a única desvantagem será algo próximo a 9,13 leituras por linha durante a execução do código e o limite de recursividade.

--Criando uma lista de 0-9 com recursividade.
;WITH Lista AS
(
  SELECT 0 AS ID
UNION ALL
  SELECT ID + 1
  FROM Lista
  WHERE ID < 9
)
SELECT * FROM Lista

4. Então vamos tornar o processo prático e deixar o código mais “limpo”, criarei uma função que retornara uma lista gerada com CTE, passando como argumentos o range a ser utilizado.

--Função
CREATE FUNCTION Lista(@inicial INT, @final INT)
RETURNS @Resultado TABLE (valor INT)
AS
BEGIN 
  IF @inicial <= @final
  BEGIN
    WITH CTE AS
    (
      SELECT @inicial AS valor
      UNION ALL
        SELECT valor + 1
        FROM CTE
        WHERE valor < @final
    )
    INSERT INTO @Resultado (valor)
      SELECT valor
      FROM CTE
      OPTION (MAXRECURSION 0)
  END
  ELSE 
  BEGIN
    WITH CTE AS
    (
      SELECT @inicial AS valor
      UNION ALL
        SELECT valor - 1
        FROM CTE
        WHERE valor > @final
    )
    INSERT INTO @Resultado (valor)
      SELECT valor
      FROM CTE
      OPTION (MAXRECURSION 0)
  END   
  RETURN;
END;

E as chamadas para os ranges variados.

SELECT valor FROM dbo.Lista(-10, 100000);
SELECT valor FROM dbo.Lista(10, -100000);

5. Mas no final a melhor alternativa é criar uma tabela no banco de dados com os valores que serão utilizados.

Se alguém tiver alguma outra alternativa, estou disposto a testar. Para o próximo artigo, estou pensando em funções para validação de valores numéricos.

Artigos relacionados:

CTE – Introdução:
https://sqlfromhell.wordpress.com/2009/09/20/cte-introducao/

WHILE vs CTE – Popular tabela de testes:
https://sqlfromhell.wordpress.com/2009/09/12/while-vs-cte-popular-tabela-de-testes/

Trabalhando com Datas – Lista de meses e dias da semana:
https://sqlfromhell.wordpress.com/2009/08/15/trabalhando-com-datas-lista-de-meses-e-dias-da-semana/

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s