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
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 iterating through table

Author  Topic 

versus69
Starting Member

3 Posts

Posted - 2007-07-10 : 03:34:01
how do you iterate through each record in a table within a user-defined function...

bit new to sql server so need some help asp. thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-10 : 04:07:08
Maybe a SELECT statement?

But with Microsoft SQL Server you have better performance using set-based queries. Maybe if you tell us what you are trying to accomplish, we might be able to help you with a better solution?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

versus69
Starting Member

3 Posts

Posted - 2007-07-10 : 21:58:45
ok well i have a table with columns

Id,Name,ParentId and i want to be able to pass an Id to the function and that it returns all its childs.

For example:
1, Steven, NULL
2, Joe, NULL
3, Chris, 1
4, Mark, 1
5, Joseph, 4
6, Daniel, 2

so then if i pass in the value 1 because i want to k now all children for Steven then it will return Chris, Mark, Joseph as joseph is a child of mark. hope you understand what im talking about now
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-11 : 01:11:02
Refer this
http://msdn2.microsoft.com/en-us/library/ms186243.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

versus69
Starting Member

3 Posts

Posted - 2007-07-18 : 23:44:54
ok now since i am new to sql server and just can do basic stuff...

how do you create these CTE's. if i go to stored procedures and ask to add a new one and then use the code it says it cant because it says
SQL batch must begin with CREATE or ALTER
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-19 : 04:51:50
quote:
Originally posted by versus69

ok now since i am new to sql server and just can do basic stuff...

how do you create these CTE's. if i go to stored procedures and ask to add a new one and then use the code it says it cant because it says
SQL batch must begin with CREATE or ALTER


Post the code you used

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

cas_o
Posting Yak Master

154 Posts

Posted - 2007-07-19 : 06:01:39
Or you could add a lineage column.


CREATE TABLE tbPeople
(
[ID] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] varchar(64) NOT NULL,
[ParentId] int NULL,
[Lineage] varchar(255) NULL
)
GO

INSERT INTO tbPeople ([Name], [ParentId])
SELECT 'Steven', NULL
UNION ALL
SELECT 'Joe', NULL
UNION ALL
SELECT 'Chris', 1
UNION ALL
SELECT 'Mark', 1
UNION ALL
SELECT 'Joseph', 4
UNION ALL
SELECT 'Daniel', 2

UPDATE tbPeople SET [Lineage] = '/'+LTRIM(STR([ID]))+'/'
WHERE [ParentId] IS NULL

UPDATE c SET [Lineage] = p.Lineage+LTRIM(STR(c.[ID]))+'/'
FROM tbPeople p JOIN tbPeople c ON p.[ID] = c.[ParentId]
WHERE c.[ParentId] IS NOT NULL

CREATE PROCEDURE usp_GetPeople
@ID int
AS
DECLARE @LINEAGE varchar(255)
SELECT @LINEAGE = [Lineage]+'%' FROM tbPeople WHERE [ID]=@ID
SELECT * FROM tbPeople WHERE [Lineage] like @LINEAGE
GO

EXEC usp_GetPeople 1


;-]... Quack Waddle
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 06:22:43
Great! That will work for two levels
Try this
-- Prepare sample data
DECLARE @People TABLE
(
[ID] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] varchar(64) NOT NULL,
[ParentId] int NULL,
[Lineage] varchar(255) NULL
)

INSERT @People ([Name], [ParentId])
SELECT 'Steven', NULL UNION ALL
SELECT 'Joe', NULL UNION ALL
SELECT 'Chris', 1 UNION ALL
SELECT 'Mark', 1 UNION ALL
SELECT 'Joseph', 4 UNION ALL
SELECT 'Daniel', 2

;WITH lineage(Name, ID, ParentID, lineage)
AS (SELECT p.Name,
p.ID,
p.ParentID,
CONVERT(varchar(255), '\' + p.Name)
FROM @People AS p
WHERE p.ParentID IS NULL -- Here is where you change you serach criteria to equal an id instead
UNION ALL
SELECT x.Name,
x.ID,
x.ParentID,
CONVERT (varchar(255), l.lineage + '\' + x.Name)
FROM @People as x
INNER JOIN lineage AS l ON x.parentID = l.ID
)

-- show the output
select id, name, parentid, lineage from lineage order by id

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 06:23:18
[code]id name parentid lineage
------- ------ ---- --------------------
1 Steven NULL \Steven
2 Joe NULL \Joe
3 Chris 1 \Steven\Chris
4 Mark 1 \Steven\Mark
5 Joseph 4 \Steven\Mark\Joseph
6 Daniel 2 \Joe\Daniel[/code]
Peter Larsson
Helsingborg, Sweden
Go to Top of Page

cas_o
Posting Yak Master

154 Posts

Posted - 2007-07-19 : 07:12:47
quote:
Originally posted by Peso

Great! That will work for two levels

...

Peter Larsson
Helsingborg, Sweden



Yes you're right, Thanks for the solution I wasn't even close yet. I have a trigger in mind to maintain the column too.

;-]... Quack Waddle
Go to Top of Page
   

- Advertisement -