Recursive SQL Function

Question

0.00/5 (No votes)

See more:

Hi,

I have two tables and I need to write a recursive function.

PRODUCT with columns 
PRODUCT_ID varchar(50), PRIMARYKEY and 
PRODUCT_GROUP_ID varchar (200)

PRODUCT_GROUP with columns
PRODUCT_GROUP_ID varchar(50), PRIMARY KEY and 
PRODUCT_UPPER_GROUP_ID varchar (200)

Here is data for PRODUCT table:

PRODUCT_ID	PRODUCT_GROUP_ID
SonyTFT3500	SonySiyah

and here is the data for PRODUCT_GROUP table:

PRODUCT_GROUP_ID	PRODUCT_UPPER_GROUP_ID
Elektronik,		null
Ev Elektroniği,		Elektronik
TV,			Ev Elektroniği
Sony,			TV
SonySiyah,		Sony

Except Elektronik, all the other PRODUCT_GROUP_IDs have an upper node. What I need is a function given a PRODUCT_ID = 'SonyTFT3500' which returns something like this:

temp = "Elektronik > Ev Elektroniği > TV > Sony > SonySiyah". 
temp2 = "Elektronik > Ev Elektroniği > TV > Sony > SonySiyah > subnode > subnode > .. > ... > ...."

You can think of PRODUCT_GROUP_ID as a parentcategory which might have a category and category might have a subcategory and subcategory has its own subcategory and this goes like this indefinitely.

How do I write a recursive function in Sql Server 2008 so that I get this string temp ?

Posted 28-Jan-11 4:03am

Close Network

Updated 28-Jan-11 4:10am

Sandeep Mewara

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manas Bhardwaj · Answer 1 · 2011-01-28T05:06:00

CREATE FUNCTION [dbo].[GetUpperProductNode]
(
	@PRODUCT_GROUP_ID NVARCHAR(100)
)
RETURNS NVARCHAR(100)
AS
BEGIN
	DECLARE @PRODUCT_UPPER_GROUP_ID NVARCHAR(100)
	SELECT @PRODUCT_UPPER_GROUP_ID = PRODUCT_UPPER_GROUP_ID FROM PRODUCT_GROUP WHERE PRODUCT_GROUP_ID = @PRODUCT_GROUP_ID
	
	RETURN @PRODUCT_UPPER_GROUP_ID

END

SQL

DECLARE @ReachedTop BIT
SET @ReachedTop = 0
CREATE TABLE #Result (PRODUCT_UPPER_GROUP_ID NVARCHAR(100))
DECLARE @PRODUCT_UPPER_GROUP_ID NVARCHAR(100)
SET @PRODUCT_UPPER_GROUP_ID = 'SonySiyah'
INSERT #Result SELECT @PRODUCT_UPPER_GROUP_ID
WHILE (@ReachedTop = 0)
BEGIN
    PRINT @PRODUCT_UPPER_GROUP_ID
    SET @PRODUCT_UPPER_GROUP_ID = (SELECT dbo.GetUpperProductNode(@PRODUCT_UPPER_GROUP_ID))
    IF (@PRODUCT_UPPER_GROUP_ID IS NULL)
        SET @ReachedTop = 1
    ELSE
        INSERT #Result SELECT @PRODUCT_UPPER_GROUP_ID
END
SELECT * FROM #Result
DROP TABLE #Result

Espen Harlinn · Answer 2 · 2011-01-28T06:53:00

Solution 2

Take a look at Common Table Expressions (CTE)
WITH common_table_expression (Transact-SQL)[^]

http://www.4guysfromrolla.com/webtech/071906-1.shtml[^]

When you are done, you should be able to easily solve your problem.

Regards
Espen Harlinn

Posted 28-Jan-11 6:53am

Espen Harlinn