• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Sql stored procedure

Hi,

I need some help in writing a stored procedure that will give me the last column.  I need to calcuate the New Name, which  depends on wether there is a parent id or not.  The parent id can keep going longer.  But this is just an example. So essentially I need a stored procedure to calculate the last column which is New Name

I am attaching the table in an excel file just for convenience.  Any help would be appreciated.

Thanks,
Joy
Book1.xlsx
0
joygomez
Asked:
joygomez
  • 6
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
;

Open in new window

0
 
joygomezAuthor Commented:
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....
0
 
Kevin CrossChief Technology OfficerCommented:
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).
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
joygomezAuthor Commented:
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
;
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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

Open in new window

0
 
joygomezAuthor Commented:
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.....
0
 
Kevin CrossChief Technology OfficerCommented:

ALTER 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), Name)
   FROM BarcodeTypes
   WHERE [ParentId] IS NULL

   UNION ALL -- starts 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 *
FROM cte
;
END
GO

-- test results
EXEC dbo.Intuit_Fix_Item_Names;

Open in new window

0
 
joygomezAuthor Commented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome.

This is DB2, but Kent did a wonderful job explaining and the principle works in SQL Server.
http://www.experts-exchange.com/A_3618.html
http://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

Open 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]

Open in new window


Hope that helps a little.

Best regards and happy coding,

Kevin
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now