Sql stored procedure

With SQL 2008, you can use a recursive CTE to accomplish this since it appears you need to calculate "New Name" downward to include the full path back through original ancestor/parent.

;WITH cte(ID, Name, [Parent ID], [New Name]) 
AS 
(
   /* Anchor query: top-level parents. */
   SELECT ID, Name, [Parent ID]
        , CONVERT(VARCHAR(255), NULL)
   FROM EERecursionEx
   WHERE [Parent ID] IS NULL

   UNION ALL -- starts recursion

   /* Recursive query: add children. */
   SELECT c.ID, c.Name, c.[Parent ID]
        , CONVERT(VARCHAR(255), COALESCE(p.[New Name], p.[Name])+': '+c.Name)
   FROM EERecursionEx c
   JOIN cte p ON p.[ID] = c.[Parent ID]
)
SELECT *
FROM cte
;

Select allOpen in new window

Hi,
I am quite new to sql.... but i just tried copying this code and ran it... but give me an error...

is the semi colon that you start with part of the code.  this is where the error is.
Thanks a lot....

Yes, it is part of the code. What is the error you get and of course you replaced EERecursionEx with your actual table name, correct? Please post the exact query you tried and the error message you get please. Check your database compatibility level is set to at least SQL 2005 (90).

yes... here is the code exactly... my table names and the columns are different from what i said before, but you will understand....and i get this error.

Msg 102, Level 15, State 1, Procedure Intuit_Fix_Item_Names, Line 5
Incorrect syntax near ';'.


CREATE PROCEDURE [dbo].[Intuit_Fix_Item_Names]


;WITH cte(Type, TypeName, [ParentId], [NewName])
AS
(
   /* Anchor query: top-level parents. */
   SELECT Type, TypeName, [ParentId]
        , CONVERT(VARCHAR(255), NULL)
   FROM BarcodeTypes
   WHERE [ParentId] IS NULL

   UNION ALL -- starts recursion

   /* Recursive query: add children. */
   SELECT c.Type, c.TypeName, c.[ParentId]
        , CONVERT(VARCHAR(255), COALESCE(p.[New Name], p.[TypeName])+': '+c.TypeName)
   FROM BarcodeTypes c
   JOIN cte p ON p.[Type] = c.[ParentId]
)
SELECT *
FROM cte
;

Okay. You did say a stored procedure. The semi-colon is needed to ensure that previous statements are closed before the CTE. It may not be needed in procedure. Plus I believe you are missing AS.

Try it like this:

CREATE PROCEDURE [dbo].[Intuit_Fix_Item_Names] 
AS
BEGIN
SET NOCOUNT ON

;WITH cte(Type, TypeName, [ParentId], [NewName]) 
AS 
(
   /* Anchor query: top-level parents. */
   SELECT Type, TypeName, [ParentId]
        , CONVERT(VARCHAR(255), NULL)
   FROM BarcodeTypes
   WHERE [ParentId] IS NULL

   UNION ALL -- starts recursion

   /* Recursive query: add children. */
   SELECT c.Type, c.TypeName, c.[ParentId]
        , CONVERT(VARCHAR(255), COALESCE(p.[NewName], p.[TypeName])+': '+c.TypeName)
   FROM BarcodeTypes c
   JOIN cte p ON p.[Type] = c.[ParentId]
)
SELECT *
FROM cte
;
END
GO

Select allOpen in new window

that worked great!!!   but still missing one more thing, the columns that do not have a parentid shows null.  how can i place the TypeName in the column NewName...

Thanks so much again.....

This was just great.... you saved me a ton of time... i had no idea how to do this and still don't, but works like a charm.

Thank you.
Joy

You are welcome.

This is DB2, but Kent did a wonderful job explaining and the principle works in SQL Server.
https://www.experts-exchange.com/A_3618.html
https://www.experts-exchange.com/A_3629.html

Aside from general knowledge of recursive CTE, here is a little further notes on your specific solution.

The first query establishes a base set of rows. In your case it is easy as those are records with NULL ParentID values.

   /* Anchor query: top-level parents. */
   SELECT Type, TypeName, [ParentId]
        , CONVERT(VARCHAR(255), Name)
   FROM BarcodeTypes
   WHERE [ParentId] IS NULL

Select allOpen in new window

Because the common table expression (CTE) which I aliased simply as "cte" is a virtual table, when used in the recursive portion of the query it has visibility to new rows just added. So on the first recursion those rows are your top-level records not having a parent. So this grabs children of the top-level rows. Now recursion continues on the new rows just added, so you get children of the children and so on until you reach the limiting criteria. In this case, it is the JOIN. Once you reach a level which has no child rows then the JOIN will result in an empty row set and stop the recursion.

   /* Recursive query: add children. */
   SELECT c.Type, c.TypeName, c.[ParentId]
        , CONVERT(VARCHAR(255), p.[NewName]+': '+c.Name)
   FROM BarcodeTypes c
   JOIN cte p ON p.[Type] = c.[ParentId]

Select allOpen in new window

Hope that helps a little.

Best regards and happy coding,

Kevin