Query SQL recursivo

Hola amigos programadores bienvenidos a nuestro blog, en este post veremos un ejemplo para generar en SQL un query que sea recursivo.

Primero vamos a explicar el concepto de recursividad, esto quiere decir que podemos entrar a una misma función muchas veces como por ejemplo el hacer una espiral, calcular el factorial de un número. Para fines más gráficos es como las muñecas rusas Matrioshka

En este post, les voy a compartir un ejemplo de como poder hacer un Store Procedure de query recursivo en SQL para la tabla de estructuras de QAD.

Antes de continuar les voy a explicar que es un Store Procedure o Procedimiento Almacenado, de manera sencilla son funciones que guardamos en nuestra basde de datos y que podemos ejecutar enviando parámetros, el objetivo es que si ya tenemos una consulta que utilizamos frecuentemente o un programa que va a hacer una consulta a la base de datos, en ocasiones es mejor llamar solo a la función que es nuestro Store Procedure que escribir toda la consulta que queremos realizar. Otra ventaja es que si tenemos SQL Server instalado, podemos programar la ejecución periódica por medio de un JOB. 

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15

Sigamos con el ejemplo:

Script

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_getpsmstr (
@domain varchar="domain",
@item   varchar="item"
)
AS
BEGIN
DECLARE @level integer;  
SET @level = 1;
WITH psmstr1 AS
( 

SELECT ps_par, ps_comp, ps_qty_per, ps_op,ps_start, ps_end
FROM dbo.ps_mstr 
WHERE ps_domain = @domain and
ps_par = @item
UNION ALL
--recursivo hasta que 
SELECT a.ps_par, a.ps_comp, a.ps_qty_per, a.ps_op,a.ps_start, a.ps_end
FROM dbo.ps_mstr a 
INNER JOIN psmstr1 s ON a.ps_par = s.ps_comp  --esta es la parte donde se busca el dato sobre la misma tabla
WHERE ps_domain = @domain
) 
select * from psmstr1
END

GO

Ejecución

Hay varias tablas de bases de datos que por alguna razón de diseño son recursivas y la forma de extraer información de ellas es creando un buffer de la misma tabla y consultarla con un campo como llave de la misma tabla. 
En mi vida he visto 2 tablas así, pero es bueno tener este tipo de consultas que nos ayuden a extraer información y no morir en el intento.