Como crear un CTE Recursivo SQListo: CTE Recursivo SQListo

JuanCastillo 27 marzo, 20176 noviembre, 2017 0 comentarios T-SQL

¿Quiero hacer un ciclo por Numero o Fechas, como lo hago?

CTE y SQListo!!!

Para crear un CTE Recursivo SQListo primero debemos entender que es un CTE, segun la ayuda oficial de microsoft dice:

«WITH common_table_expression (Transact-SQL) Especifica un conjunto de resultados temporal con nombre, conocido como expresión de tabla común (CTE). Se deriva de una consulta simple y se define en el ámbito de ejecución de una sola instrucción SELECT, INSERT, UPDATE o DELETE».

WITH CTE_NUMERO ( Numero )
AS
(
SELECT 1
UNION ALL
SELECT Numero +1
FROM CTE_NUMERO 
)
SELECT Numero FROM CTE_NUMERO

WITH CTE_NUMERO ( Numero )

(

SELECT 1

UNION ALL

SELECT Numero +1

FROM CTE_NUMERO

)

SELECT Numero FROM CTE_NUMERO

Este, CTE demuestra lo que ocurre cuando se codifica un bucle infinito. Este CTE sólo genera una lista de números a partir de 1 e incrementa 1 por cada nuevo número devuelto por el CTE. Debido a que la parte recursiva de este CTE no indica un punto de parada, la configuración MAXRECUSION controlará cuando esta consulta de recursión se detendrá.

MAXRECUSION se puede utilizar para controlar el número veces la parte recursiva de la consulta (la instrucción SELECT que hace referencia al CTE) se ejecuta. El valor predeterminado para MAXRECUSION es 100. Si ejecuta el código anterior, verá que después de 100 ejecuciones de la segunda instrucción SELECT en «CTE_NUMERO » se producirá el siguiente error:

 Msg 530, Nivel 16, Estado 1, Línea 1

1	Msg 530, Nivel 16, Estado 1, Línea 1

La declaración terminó. La recursión máxima 100 se ha agotado antes de completar la declaración.

Después de que se produzca el error, si observa la salida producida, verá que se crearon 101 números diferentes.

Si la lógica de su CTE queridos lectores requieren más iteración que el valor predeterminado para MAXRECUSION, puede reemplazar el número predeterminado mediante la sugerencia de consulta MAXRECURSION.

WITH CTE_NUMERO ( Numero )
AS
(
SELECT 1
UNION ALL
SELECT Numero +1
FROM CTE_NUMERO 
)
SELECT Numero FROM CTE_NUMERO 

 
OPTION(MAXRECURSION 200);<b style="font-family: verdana, arial, helvetica; font-size: 13px; white-space: pre-wrap;"> </b>

WITH CTE_NUMERO ( Numero )

(

SELECT 1

UNION ALL

SELECT Numero +1

FROM CTE_NUMERO

)

SELECT Numero FROM CTE_NUMERO

OPTION(MAXRECURSION 200);<b style="font-family: verdana, arial, helvetica; font-size: 13px; white-space: pre-wrap;"> </b>

Aquí tienen que tener en cuenta que como es un bucle infinito seguiremos teniendo el mismo error Msg 530, Nivel 16, Estado 1, Línea 1 para resolver que no tengamos este error modificaremos

WITH CTE_NUMERO ( Numero )
AS
(
SELECT 1
UNION ALL
SELECT Numero +1
FROM CTE_NUMERO 
        WHERE Numero 
)
SELECT Numero FROM CTE_NUMERO 
<b>OPTION</b>(MAXRECURSION 200);

WITH CTE_NUMERO ( Numero )

(

SELECT 1

UNION ALL

SELECT Numero +1

FROM CTE_NUMERO

WHERE Numero

)

SELECT Numero FROM CTE_NUMERO

<b>OPTION</b>(MAXRECURSION 200);

1. El Máximo en MaxRecursion es de 32.767

1. Pueden colocar MaxRecursion puede ser Cero y no tendrá limite,(CUIDADO con esta opción pueden dejar al server sin memoria)

1. Luego de 1000 itereaciones el CTE crea en la base de datos tempdb un cursos interno para manejar los registros del mismo

Los CTE son muy parecidos a crear una vista o una tabla temporal, pero pueden ser llamados recursivamente. Ser capaz de definir múltiples CTEs que unen dos complicadas instrucciones T-SQL hace que su código T-SQL sea más fácil de escribir, depurar y mantener.