Finding nested references - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Finding nested references

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2008-08-04 : 07:34:18

Hi,

I've got a table (outlined below) that contains a list of URLs, the idea is that it allows us to keep out dated URLs in the database to redirect the search engines accordingly (i.e. to the new URL).

The issue I have is that some of the URLs link to other URLs rather than the object themselves, what I would like to do in that instance is update them to point to the item instead of a URL, i.e. in the example below the URL '/' should point to '/another-link/' instead of '/some-link/'.

Thanks to poor historic data in this table, the object link could be several links deep so I think I need some form of recursion to find the object id?

We're using SQL Server 2005 so I thought some form of CTE might also work?

TIA

Tim

CREATE TABLE #Url(
	Id int,
	Url varchar(100),
	ObjectTypeId int,
	ObjectId int
)

--ObjectTypeId Narative
-- 1 : Url
-- 2 : Object


INSERT INTO #Url VALUES (1, '/', 1, 2)
INSERT INTO #Url VALUES (2, '/some-link/', 1, 3)
INSERT INTO #Url VALUES (3, '/another-link/', 2, 999)

SELECT
	*
FROM
	#Url

DROP TABLE #Url

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

timgaunt
Posting Yak Master

115 Posts

Posted - 2008-08-06 : 06:53:04

I've sorted this now but writing a UDF that looks it up, if anyone else is needing to do something similar here it is:


ALTER FUNCTION [dbo].[GetMostRecentURLById] (
	@Id int
) 
RETURNS INT 
AS 
BEGIN
	DECLARE @rewriteID int, @superseededBy int 

	IF EXISTS(SELECT UrlId FROM Url WHERE UrlId = @Id AND ObjectType = 1)
	  BEGIN
			--This is a URL
			SELECT 
				@rewriteID = @Id, 
				@superseededBy = dbo.GetMostRecentURLById(ObjectId) 
			FROM 
				Url
			WHERE
				UrlId = @Id
			  AND
				ObjectType = 1

			IF @superseededBy IS NOT NULL 
				RETURN @superseededBy 
			ELSE 
				RETURN @rewriteID 
	  END
	ELSE IF EXISTS(SELECT UrlId FROM Url WHERE UrlId = @Id AND ObjectType <> 1)
	  BEGIN
			--This is NOT a URL
			RETURN @Id

	  END

  RETURN NULL

END

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources