sql loop - 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

New to SQL Server Programming

sql loop

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-12-26 : 06:46:06

Hi i have the query below in a stored proc, i need to loop through this an update the Ordinal column with a count ie 1,2,3 etc

not sure how to loop through i have tried a few differnt things?

SELECT t.Id, t.Ordinal
FROM dbo.Tree t
INNER JOIN dbo.Page p ON p.Id = t.PageId
WHERE p.ParentId = @ParentId
Order By t.Ordinal

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-26 : 07:06:17

Hi,

Please post your table structures,sample data and expected output so that u will get ur question answered quickly...

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-12-26 : 07:25:08

all i need to know is how to loop through the query, the update statement will be this

@count int = 0

START WHILE LOOP
@count = @count +1

Update Tree
SET Ordinal = @count
WHERE Id = --THE ID FROM THE SELECT QUERY

END WHILE LOOP

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-26 : 23:12:18

declare @cnt int
set @cnt = 1

while(@cnt > 3)--(give the value how many times u want the loop to execute)
begin
Update Tree
SET Ordinal = @count
WHERE Id = --THE ID FROM THE SELECT QUERY

select @cnt = @cnt + 1
end
try like this

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-27 : 00:53:34

declare @count int
set @count=0
Update Tree
SET Ordinal=@count, @count=@count+1
WHERE Id = --THE

Madhivanan

Failing to plan is Planning to fail

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-12-27 : 06:40:26

Hi i know how to loop i need to know how to loop through a query

so this is what i need

1. SELECT QUERY
SELECT t.Id, t.Ordinal
FROM dbo.Tree t
INNER JOIN dbo.Page p ON p.Id = t.PageId
WHERE p.ParentId = @ParentId
Order By t.Ordinal

2. LOOP THROUGH THE QUERY ABOVE

@count int = 0

START LOOP
@count = @count +1

Update Tree
SET Ordinal = @count
WHERE Id = --THE ID FROM THE SELECT QUERY

END LOOP

So i need to know how to loop through the query and pass the Id to the update statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-27 : 11:58:48

seems like what you need is this


UPDATE r
SET r.Ordinal=r.Seq
FROM
(
SELECT t.Id, t.Ordinal,
ROW_NUMBER() OVER(ORDER BY t.Ordinal) AS Seq
FROM dbo.Tree t
INNER JOIN dbo.Page p 
ON p.Id = t.PageId
WHERE p.ParentId = @ParentId
)r

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources