Recursive Querying in SQL Server
In this post we will discuss about the use of recursive querying in SQL Server.
Recently, I got a request from a Partner to develop a SSRS report for the Indented Bill of Materials in the Inventory module. I was going through the Dexterity Indented Bill of Materials report and found out that the report uses a temporary table. For filling the temporary table with the Inventory Bill of Materials hierarchy, Dynamics GP recursively loops around a logic written in Dexterity. We are stuck here, because we won’t be able to consume the logic that Dynamics GP uses to build the Inventory Bill of Materials hierarchy in SSRS. But, luckily we can create a recursive query in SQL Server to get the Inventory Bill of Materials hierarchy without spending much time.
Recursive Query to get the Inventory Bill of Materials hierarchy
DECLARE @billNumber CHAR (31)
DECLARE @BMReporting TABLE
(
ITEMNMBR CHAR (31),
CMPTITNM CHAR (31),
ITEMDESC CHAR (111)
)
SELECT @billNumber = 'FAXX-FG3-0001';
-- By Recursive Query using CTE build the Indented BOM
WITH BMReporting (ITEMNMBR, CMPTITNM, ITEMDESC)
AS (
-- Anchor member definition
SELECT CAST (NULL AS VARCHAR (64)) AS ITEMNMBR,
CAST (LTRIM (RTRIM (BMHDR.ITEMNMBR)) AS VARCHAR (64)) AS CMPTITNM,
LTRIM (RTRIM (ITMMSTR.ITEMDESC)) AS ITEMDESC
FROM dbo.BM00101 BMHDR
INNER JOIN IV00101 ITMMSTR ON BMHDR.ITEMNMBR = ITMMSTR.ITEMNMBR
WHERE BMHDR.ITEMNMBR = @billNumber
AND BMHDR.Bill_Status = 1
UNION ALL
-- Recursive member definition
SELECT CAST (RTRIM (LTRIM (BMDTL.ITEMNMBR)) AS VARCHAR (64)),
CAST (RTRIM (LTRIM (BMDTL.CMPTITNM)) AS VARCHAR (64)),
LTRIM (RTRIM (ITMMSTR.ITEMDESC))
FROM dbo.BM00111 AS BMDTL
INNER JOIN dbo.IV00101 ITMMSTR ON ITMMSTR.ITEMNMBR = BMDTL.CMPTITNM
INNER JOIN BMReporting AS BMReporting ON BMDTL.ITEMNMBR = BMReporting.CMPTITNM
WHERE BMDTL.Bill_Status = 1
)
-- Statement that executes the CTE
INSERT INTO @BMReporting
SELECT ITEMNMBR,
CMPTITNM,
ITEMDESC
FROM BMReporting
SELECT * FROM @BMReporting
--@billNumber :- The Bill Number for which need the hierarchy
Please refer the following link for more information on Recursive Querying in SQL Server.
https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
Using the above recursive query as a base, built a Stored Procedure that accepts ‘Bill Number’ as an input parameter and returns the Inventory Bill of Materials hierarchy as a result set.
I have attached the SQL object and RDL file developed along with this post for your reference.
Hope this helps...
Until next post !!
// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)