Uma imaginação bem fértil para usar LIKE

O que Joãozinho faz quando ele não quer usar o IN e tem uma imaginação bem fértil para usar LIKE?

Ele torna um simples IN:

SELECT * FROM Clientes
WHERE Codigo IN (12, 34, 19)

Em um LIKE “bem” elaborado:

SELECT * FROM Clientes
WHERE ' 12, 34, 19,' LIKE '% ' + CAST(Codigo AS VARCHAR) + ',%'

Agora que Joãozinho esta feliz, vamos ver o plano de execução?

Como Joãozinho conseguiu transformar um simples “clustered index seek” em um “clustered index scan” com custo aproximadamente 99x maior que a consulta com IN, digamos que esta na hora do Joãozinho parar de inventar moda… 🙂

FUNÇÕES: Somente Números (com CTE) Versão 2

[2011-04-02] Código completo – Somente números:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[0-9]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[2011-04-02] Código completo – Somente texto:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[A-z]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[2011-04-02] Código completo – Remover caracteres especiais:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += (CASE WHEN LETRA LIKE '[A-z0-9]' THEN LETRA ELSE ' ' END)
FROM SPLIT
OPTION(MAXRECURSION 0)

SELECT @RESULTADO
[Post-Original] Explicação:

Boa noite pessoas!

Já faz algum tempo que escrevi as funções “somente números” com WHILE e CTE, até que estes dias eu tive de reformular elas de forma a remover caracteres especiais, remover máscaras e afins, assim como para outras funcionalidades mais absurdas… O legal foi que causou uma leve mudança da estrutura original da minha CTE antiga, resultando numa terceira função “somente números” bem interessante, que facilmente pode ser tranformada em “somente letras”, “somente números e letras”, “somente caractéres internacionais”… Ok, famos ao que interessa!

Primeiramente temos o texto:

DECLARE @Texto NVARCHAR(MAX)
SET @Texto = N'O azul é uma das três cores-luz primárias,
e cor-pigmento secundária, resultado da sobreposição dos
pigmentos ciano e magenta. Seu comprimento de onda é da
ordem de 455 a 492 nanômetros do espectro de cores visíveis.';

Em seguida uma variável que receberá o resultado:

DECLARE @Result NVARCHAR(MAX)
SET @Result = ''

E a expressão/CTE de split de caractere por caractere:

;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@Texto, 1, 1) AS CH
UNION ALL
SELECT ID + 1, SUBSTRING(@Texto, ID + 1, 1)
FROM SPLIT
WHERE ID < LEN(@Texto)
)

E agora para filtrar e juntar todos os caracteres:

SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[0-9]'
OPTION (MAXRECURSION 0)

Enfim exibindo o resultado:

SELECT @Result AS [Resultado]

Também é possível adaptar a consulta para “somente letras” ou “somente alfa-numéricos” (removendo os caracteres especiais):

SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[A-z]'
OPTION (MAXRECURSION 0)

-- OU

SELECT @Result = @Result + CH
FROM SPLIT
WHERE CH LIKE '[A-z0-9]'
OPTION (MAXRECURSION 0)

Mas estas consultas também podem ter mais utilidade com um CASE substituindo os caracteres especiais por espaço ou outro caractere qualquer:

SELECT @Result = @Result
+ (CASE WHEN CH LIKE '[A-z0-9]' THEN CH ELSE '' END)
FROM SPLIT
OPTION (MAXRECURSION 0)

Mas sempre é bom lembrar que é melhor ter rotinas deste gênero na aplicação no lugar do banco de dados ou pensar em CLR.

Obrigado Eduardo Ruthes pela idéia inicial desta CTE!!!

Artigos relacionados:

FUNÇÕES: Validação de CNPJ e CPF
https://sqlfromhell.wordpress.com/2009/11/19/validacao-de-cnpj-e-cpf/

FUNÇÕES: Somente Números (com CTE) [Original]
https://sqlfromhell.wordpress.com/2009/11/18/somente-numeros-com-cte/

FUNÇÕES: Somente Números (com WHILE)
https://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/

FUNÇÕES: Validação de CNPJ e CPF com T-SQL

[2011-04-02] Código completo:

DECLARE @TEXTO VARCHAR(20)
SET @TEXTO = '02.841.834/0001-55'

DECLARE @CPF_CNPJ VARCHAR(20)
SET @CPF_CNPJ = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @CPF_CNPJ += LETRA
FROM SPLIT
WHERE LETRA LIKE '[0-9]'
OPTION(MAXRECURSION 0)

IF LEN(@CPF_CNPJ) NOT IN (11, 14)
BEGIN
	SELECT 'Inválido'
	RETURN
END

DECLARE
	@DIGITO1 INT,
	@DIGITO2 INT,
	@VALOR1 INT,
	@VALOR2 INT

DECLARE
	@I INT,
	@J INT,
	@TOTAL_TMP INT,
	@COEFICIENTE_TMP INT,
	@DIGITO_TMP INT,
	@VALOR_TMP INT

SET @DIGITO1 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1)
SET @DIGITO2 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1)
SET @J = 1

WHILE @J <= 2 BEGIN 	SELECT 		@TOTAL_TMP = 0, 		@COEFICIENTE_TMP = 2 	SET @I = ((LEN(@CPF_CNPJ) - 3) + @J) 	WHILE @I >= 0
	BEGIN
		SELECT
			@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
			@TOTAL_TMP = @TOTAL_TMP + (@DIGITO_TMP * @COEFICIENTE_TMP),
			@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1

		IF (@COEFICIENTE_TMP > 9) AND LEN(@CPF_CNPJ) = 14
			SET @COEFICIENTE_TMP = 2
		SET @I = @I - 1
	END

	SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)

	IF (@VALOR_TMP >= 10)
		SET @VALOR_TMP = 0

	IF @J = 1
		SET @VALOR1 = @VALOR_TMP
	ELSE
		SET @VALOR2 = @VALOR_TMP
	SET @J = @J + 1
END

SELECT
	CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
		THEN 'Válido'
		ELSE 'Inválido'
	END

[Post-Original] Explicação:

Boa noite pessoas!

No blog do Rogério, encontrei um ótimo algoritmo de validação de CNPJ e CPF para ORACLE, para que ninguém tenha que ficar transitando de ‘PL\SQL’ para ‘T-SQL’, já realizei este trabalho e vou detalhar um pouco o funcionamento deste algoritmo.

Primeiro precisamos de um CNPJ ou CPF para validar, assim identifiquei um que estava “vagando” na internet.

DECLARE @CPF_CNPJ VARCHAR(20)
SET @CPF_CNPJ = '02.841.834/0001-55'

Ok, tenho o CNPJ/CPF para validar, mas tenho que remover os caracteres não numéricos, desta forma utilizarei a função SOMENTE_NUMEROS que desenvolvi para o artigo anterior:

SET @CPF_CNPJ = dbo.SOMENTE_NUMEROS(@CPF_CNPJ)

A primeira valiação verifica se a quantidade de caracteres é de um CPF ou de um CNPJ

IF LEN(@CPF_CNPJ) NOT IN (11, 14)
BEGIN
SELECT 'Inválido'
RETURN
END

Em sequida, defino quatro variáveis que serão utilizadas para a validação dos digitos verificadores do CNPJ e CPF.

DECLARE
@DIGITO1 INT,
@DIGITO2 INT,
@VALOR1 INT,
@VALOR2 INT

Também há outras variáveis a serem utilizadas durante a operação.

DECLARE
@I INT,
@J INT,
@TOTAL_TMP INT,
@COEFICIENTE_TMP INT,
@DIGITO_TMP INT,
@VALOR_TMP INT

As variáveis DIGITO1 e DIGITO2 armazenarão os digitos verificadores do CNPJ/CPF.

SET @DIGITO1 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1)
SET @DIGITO2 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1)

Feito isso, determino um laço de repetição para dois loops, um para cada digito.

SET @J = 1
WHILE @J <= 2
BEGIN

Determino os valores inicias das variáveis de validação.

SELECT
@TOTAL_TMP = 0,
@COEFICIENTE_TMP = 2

Agora, aplico o algoritmo para gerar cada um dos digitos verificadores.

SET @I = ((LEN(@CPF_CNPJ) - 3) + @J)
WHILE @I >= 0
BEGIN
SELECT
@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
@TOTAL_TMP = @TOTAL_TMP + (@DIGITO_TMP * @COEFICIENTE_TMP),
@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1
IF (@COEFICIENTE_TMP > 9) AND LEN(@CPF_CNPJ) = 14
SET @COEFICIENTE_TMP = 2
SET @I = @I - 1
END
 SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
IF (@VALOR_TMP >= 10)
SET @VALOR_TMP = 0

Identifico qual dos digitos verificadores foi gerado e prossigo para o próximo digito no laço de repetição.

IF @J = 1
SET @VALOR1 = @VALOR_TMP
ELSE
SET @VALOR2 = @VALOR_TMP
SET @J = @J + 1
END

Por fim, verifico se os digitos gerados têm o mesmo valor dos presente no CNPJ/CPF.

IF @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
SELECT 'Válido'
ELSE
SELECT 'Inválido'

Para o próximo artigo, ainda estou estudando uma forma interessante de trabalhar com máscaras e CTE.

Artigos relacionados:

Blog do Rogério – Validação CNPJ/CPF com PL/SQL
http://blogdozunga.blogspot.com/2009/02/precisava-de-uma-rotina-para-validar.html

FUNÇÕES: Somente Números (com CTE)
https://sqlfromhell.wordpress.com/2009/11/18/somente-numeros-com-cte/

FUNÇÕES: Somente Números (com WHILE)
https://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/

FUNÇÕES: Somente Números (com CTE)

[2011-04-02] Código completo:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE
	@RESULTADO VARCHAR (8000),
	@ZERO INT,
	@NOVE INT

SELECT
   @ZERO = UNICODE(0),
   @NOVE = UNICODE(9)

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)
, NUMEROS AS
(
	SELECT LETRA
	FROM SPLIT
	WHERE UNICODE(LETRA) BETWEEN @ZERO AND @NOVE
)

SELECT @RESULTADO = (SELECT LETRA + '' FROM NUMEROS FOR XML PATH(''))
OPTION(MAXRECURSION 0)

SELECT @RESULTADO

[2011-04-02] Código completo v2:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE	@RESULTADO VARCHAR(8000)

SET @RESULTADO = ''

;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)

SELECT @RESULTADO += LETRA
FROM SPLIT
WHERE LETRA LIKE '[0-9]'
OPTION(MAXRECURSION 0)

SELECT @RESULTADO
[Post-Original] Explicação:

Boa noite pessoal!

Continuando o artigo anterior, vou demonstrar como recuperar somente os valores numéricos de um texto, agora com CTE.

Para começar, tenho o mesmo cenário do artigo anterior, uma variável com um texto absurdo, da qual desejo somente retornar os valores numéricos.

DECLARE @PALAVRA VARCHAR(8000)
SET @PALAVRA = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'
</code></div>
Como estou utilizando CTE, não tenho necessidade de muitas variáveis.
<div style="border: 1px; background-color: #000000; color: #ffffff; font-weight: bold; border-color: #ffffff; margin: 5px; padding: 5px;"><code>DECLARE
@RESULTADO VARCHAR (8000),
@ZERO INT,
@NOVE INT

Mas ainda continuo a ter a necessidade das variáveis que delimitam o RANGE dos valores numéricos, no caso os ‘valores decimais’ dos caracteres 0 (zero) e 9 (nove):

SELECT
@ZERO = UNICODE(0),
@NOVE = UNICODE(9)

Com a primeira CTE, realizo crio a relação de um a um dos caracteres do meu texto (um caractere por linha). Na segunda CTE, aplico a validação do RANGE de cada um dos caracteres relacionados na primeira CTE.

;WITH SPLIT AS
(
SELECT 1 AS ID, SUBSTRING(@PALAVRA, 1, 1) AS LETRA
UNION ALL
SELECT ID + 1, SUBSTRING(@PALAVRA, ID + 1, 1)
FROM SPLIT
WHERE ID &lt; LEN(@PALAVRA)
)
, NUMEROS AS
(
SELECT LETRA
FROM SPLIT
WHERE UNICODE(LETRA) BETWEEN @ZERO AND @NOVE
)

Por fim, utilizo uma query para concatenar os valores resultantes da segunda CTE, adicionando o HINT MAXRECURSION, para evitar que um texto com mais de 100 caracteres prejudique minha operação.

SELECT @RESULTADO = (SELECT LETRA + '' FROM NUMEROS FOR XML PATH(''))
OPTION(MAXRECURSION 8000)

No próximo artigo trarei a validação de CPF e CNPJ em uma única operação (e de forma otimizada).

Artigos relacionados:

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

FUNÇÕES: Somente Números (com WHILE):
https://sqlfromhell.wordpress.com/2009/11/17/somente-numeros-com-while/

FUNÇÕES: Somente Números (com WHILE)

[2011-04-02] Código completo:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE
	@RESULTADO VARCHAR (8000),
	@LETRA CHAR(1),
	@NUMERO INT,
	@CONT INT,
	@ZERO INT,
	@NOVE INT

SELECT
	@RESULTADO = '',
	@CONT = 0,
	@ZERO = UNICODE(0),
	@NOVE = UNICODE(9),
	@NUMERO = LEN(@TEXTO)

WHILE @CONT < @NUMERO
BEGIN
	SET @CONT = @CONT + 1
	SET @LETRA = SUBSTRING(@TEXTO, @CONT, 1)

	IF UNICODE(@LETRA) BETWEEN @ZERO AND @NOVE
	BEGIN
		SET @RESULTADO = @RESULTADO + @LETRA
	END
END

SELECT @RESULTADO

[2011-04-02] Código completo v2:

DECLARE @TEXTO VARCHAR(8000)
SET @TEXTO = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'

DECLARE
	@RESULTADO VARCHAR (8000),
	@LETRA CHAR(1),
	@NUMERO INT,
	@CONT INT

SELECT
	@RESULTADO = '',
	@CONT = 0,
	@NUMERO = LEN(@TEXTO)

WHILE @CONT < @NUMERO
BEGIN
	SET @CONT = @CONT + 1
	SET @LETRA = SUBSTRING(@TEXTO, @CONT, 1)

	IF @LETRA LIKE '[0-9]'
	BEGIN
		SET @RESULTADO = @RESULTADO + @LETRA
	END
END

SELECT @RESULTADO
[Post-Original] Explicação:
Boa noite pessoal!
Para filtrar um texto, deixando somente os valores numéricos, pensei em realizar de duas formas, a primeira com WHILE e outra com CTE, mas como CTE se torna um pouco complexo para alguns, vou começar com WHILE:
Primeiro tenho meu texto, no caso estou utilizando a variável @PALAVRAS com um texto que encontrei na internet.
DECLARE @PALAVRA VARCHAR(8000)
SET @PALAVRA = 'Yield 96%, white crystals, mp 104 °C (acetonitrile). δH (DMSO-d6): 5.40 (1H, s, H-2), 6.54 (2H, d, J45=J89=7.8 Hz, H-4, H-9), 7.02 (2H, d, J56=J78=8.0 Hz'
Tendo o texto, vou definir quais as variáveis que vou utilizar, assim tenho uma variável onde salvarei o resultado (@RESULTADO) da operação, uma variável para armazenar uma a uma as ‘letras’ (@LETRA) da operação, uma variável para saber quantas ‘letras’ tenho no texto (@NUMERO) e outra para controle dos ‘loops’ da operação (@CONT).
DECLARE
@RESULTADO VARCHAR (8000),
@LETRA CHAR(1),
@NUMERO INT,
@CONT INT,
@ZERO INT,
@NOVE INT
Esqueci de dizem que é @ZERO e @NOVE, bem… elas irão armazenar os valores que identifica range inicial e final dos valores numéricos, no caso 0 (zero) e o 9 (nove) respectivamente.  A função UNICODE retorna o ‘valor decimal’ de um determinado caractere.
Uma observação importante é o valor inicial de @RESULTADO, pois como o valor inicial das variáveis é NULL, NULL somado a qualquer outros valor, nas configurações padrões do T-SQL, tem como resultado NULL, então precisamos estipular o valor inicial dele, assim como das outras variáveis.
SELECT
@RESULTADO = '',
@CONT = 0,
@ZERO = UNICODE(0),
@NOVE = UNICODE(9),
@NUMERO = LEN(@PALAVRA)
Vamos agora ao laço de repetição, que percorrerá uma a uma as letras.
WHILE @CONT < @NUMERO
BEGIN
SET @CONT = @CONT + 1
SET @LETRA = SUBSTRING(@PALAVRA, @CONT, 1)
Agora temos a validação, onde verifico se a variável @LETRA esta em 0 (zero) e 9 (nove). Utilizando novamente a função UNICODE, que retorna o ‘valor decimal’ de um caractere.
IF UNICODE(@LETRA) BETWEEN @ZERO AND @NOVE
BEGIN
SET @RESULTADO = @RESULTADO + @LETRA
END
END

Agora temos a primeira solução. No próximo artigo estarei detalhando a forma de realizar esta operação com CTE.

Artigos relacionados:

FUNÇÕES: Somente Números (com CTE):
https://sqlfromhell.wordpress.com/2009/11/18/somente-numeros-com-cte/

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/

WHILE vs CTE – Popular tabela de testes

Boa noite pessoas!

Por questões pessoais acabei não sendo muito fã de WHILE (laço de repetição) em código T-SQL, principalmente pelo fato de WHILE lembrar CURSOR e CURSOR levam a algumas rotinas nada performáticas, no lugar de desenvolver consultas SQL um pouco mais elaboradas.

Para popular tabelas de testes e demonstração é comum encontrar exemplo com WHILE, mas quero deixar minha proposta utilizando CTE.

No código abaixo crio uma tabela comum encontrada em demonstrações de Sql Tuning, por utilizar uma coluna do tipo char de tamanho quase absurdo.

Obs.: Se for utilizar WHILE, utilize “SET NOCOUNT ON” para evitar ter receber um monte de mensagens de “linhas afetadas”.

CREATE TABLE TabelaDeTestes
(
  idTab INT,
  textoTab CHAR(5000)
)
GO

Declaro a variável que será incrementada durante a execução do WHILE:

DECLARE @I INT
SET @I = 0

Enfim o desenvolvo o WHILE populando a tabela de teste com a variável incrementada e um texto gerado pela função NEWID (Função responsável por gerar GUID no T-SQL).

WHILE @I < 1000
BEGIN
  SET @I = @I + 1
  INSERT INTO TabelaDeTestes (idTab, textoTab)
  VALUES (@I, NEWID())
END
GO

 Ok, feito o método tradicional com WHILE, vamos para a minha proposta com CTE. Primeiramente crio um CTE recursivo. 

;WITH Cte (idTab, textoTab)
AS
(
SELECT 1, NEWID()
UNION ALL
SELECT idTab + 1, NEWID() FROM Cte
WHERE idTab <= 1000
)

Depois um tradicional “INSERT INTO tabela SELECT” com um HINT para aumentar o limite de recursividade da CTE para 1000. 

INSERT INTO TabelaDeTestes (idTab, textoTab)
SELECT idTab, textoTab FROM Cte
OPTION (MAXRECURSION 1000)

De qualquer forma CTE possui várias limitações, seja pelo número de recursividades que por padrão é 100 e com o HINT utilizado até 32767, mas seu desempenho é muito superior as ronitas desenvolvidas com o WHILE, vale a pena testar.

Espero que tenham gostado do post e os motivado a desenvolver consultas bem elaboradas em SQL, deixando sempre como última alternativa utilização de estruturas WHILE e afins, então pessoas até o próximo post!

 

Posts relacionados:

Gerando lista de meses e dias da semana com CTE:
https://sqlfromhell.wordpress.com/2009/08/15/trabalhando-com-datas-lista-de-meses-e-dias-da-semana/

Gerando consultas desordenadas e aleatórias:
https://sqlfromhell.wordpress.com/2009/07/25/consultas-desordenadas-e-aleatorias/