function to return head of parent

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

New to SQL Server Programming

function to return head of parent

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-19 : 14:49:41

I have an existing function and need to alter function to give result of the parent-description until its parent is reached.
.

--CREATE TABLE

CREATE TABLE [dbo].[CityData](
[Id] [int] NULL,
[ParentID] [int] NULL,
[City] [nchar](20) NULL,
[Location] [nchar](50) NULL,
[Amt] [int] NULL
) ON [PRIMARY]

---INSERT DATA

insert into CityData
values(1,-1,'Bangalore','BangaloreMain',20),
(2,1,'NULL','North Bangalore',10),
(3,2,'NULL','North Bangalore - Area-1',5),
(4,2,'NULL','North Bangalore - Area-2',15),
(5,1,'NULL','South Bangalore',7),
(6,-1,'Others','Coimbatore',4),
(7,6,'NULL','Coimbatore South',5),
(8,7,'NULL','Tirupur',1),
(9,7,'NULL','Avinashi',3)

----CREATE FUNCTION
CREATE FUNCTION [dbo].[GetFirstLevelLabel]
(
@cityParentId int
)
RETURNS nvarchar(128)
AS
BEGIN
DECLARE @strLabel nvarchar(128)
set @strLabel = ''

if @cityId >= 999999
set @strLabel = SELECT City FROM CityData where Id = @cityParentId
-- Need help on getting the parent id -1 in loop

else
if @depName <> ''
set @strLabel = SELECT City FROM CityData where Id = @cityParentId
else
set @strLabel = 'General'

RETURN @strLabel

END
GO

---- My Query
SELECT Id, GetFirstLevelLabel(ParentID) as City, Location, Amt
, Amt
FROM CityData

Result Expected
City Location Amt
Bangalore BangaloreMain 20
Bangalore North Bangalore 10
Bangalore North Bangalore - Area-1 5
Bangalore North Bangalore - Area-2 15
Bangalore South Bangalore 7
Others Coimbatore 4
Others Coimbatore South 5
Others Tirupur 1
Others Avinashi 3

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-19 : 20:17:15

If not must use function, select like following.

------------------------------------------------

WITH SOURCE ([Id], ParentID, City, Location, Amt) AS (
SELECT *
FROM CityData
WHERE ParentID = -1
UNION ALL
SELECT CityData.[Id], CityData.ParentID, SOURCE.City, CityData.Location, CityData.Amt
FROM CityData
INNER JOIN SOURCE
	ON SOURCE.[Id] = CityData.ParentID
)
SELECT
	City, Location, Amt
FROM SOURCE
ORDER BY SOURCE.[Id]

-------------------------------------
From Japan
Sorry, my English ability is limited.

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-20 : 00:58:48

I am having many coloumns in my result, so i need to use funnction..
kindly suggest

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-20 : 06:39:54

quote:
Originally posted by shanmugaraj

I am having many coloumns in my result, so i need to use funnction..
kindly suggest

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

DO you mean you didnt understand any of suggestions that were provided to you from the other forum?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources