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.
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 LarssonHelsingborg, Sweden |
 |
|
versus69
Starting Member
3 Posts |
Posted - 2007-07-10 : 21:58:45
|
ok well i have a table with columnsId,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, NULL2, Joe, NULL3, Chris, 14, Mark, 15, Joseph, 46, Daniel, 2so 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 |
 |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
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 saysSQL batch must begin with CREATE or ALTER |
 |
|
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 saysSQL batch must begin with CREATE or ALTER
Post the code you usedMadhivananFailing to plan is Planning to fail |
 |
|
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)GOINSERT INTO tbPeople ([Name], [ParentId])SELECT 'Steven', NULL UNION ALLSELECT 'Joe', NULL UNION ALLSELECT 'Chris', 1 UNION ALLSELECT 'Mark', 1 UNION ALLSELECT 'Joseph', 4 UNION ALLSELECT 'Daniel', 2 UPDATE tbPeople SET [Lineage] = '/'+LTRIM(STR([ID]))+'/'WHERE [ParentId] IS NULLUPDATE 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 NULLCREATE PROCEDURE usp_GetPeople @ID intASDECLARE @LINEAGE varchar(255)SELECT @LINEAGE = [Lineage]+'%' FROM tbPeople WHERE [ID]=@IDSELECT * FROM tbPeople WHERE [Lineage] like @LINEAGEGOEXEC usp_GetPeople 1 ;-]... Quack Waddle |
 |
|
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 dataDECLARE @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 ALLSELECT 'Joe', NULL UNION ALLSELECT 'Chris', 1 UNION ALLSELECT 'Mark', 1 UNION ALLSELECT 'Joseph', 4 UNION ALLSELECT '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 outputselect id, name, parentid, lineage from lineage order by id Peter LarssonHelsingborg, Sweden |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-19 : 06:23:18
|
[code]id name parentid lineage------- ------ ---- --------------------1 Steven NULL \Steven2 Joe NULL \Joe3 Chris 1 \Steven\Chris4 Mark 1 \Steven\Mark5 Joseph 4 \Steven\Mark\Joseph6 Daniel 2 \Joe\Daniel[/code]Peter LarssonHelsingborg, Sweden |
 |
|
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 LarssonHelsingborg, 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 |
 |
|
|
|
|
|
|