ROW_NUMBER in a WHILE loop

Question

Post reply

ROW_NUMBER in a WHILE loop

smorales 52862

Old Hand

Points: 339
More actions
August 18, 2014 at 4:44 am

#310821

Hello everyone!
I'm trying to get a value from a field inside a loop (each iteration, the value of the next row) and can't seem to make it work. My code is:
WHILE @Count <= @NumTables (
SET @TABLE1= RTRIM(a.DBNAME) FROM (SELECT a.DBNAME,ROW_NUMBER() OVER(ORDER BY DBNAME) AS NUMROW FROM a) WHERE NAME=@Count)
BREAK
Thanks a lot!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Row number always starts at 1 for any query.

Couple steps back, what are you trying to do here?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

smorales 52862 Old Hand Points: 339 More actions · Answer 2

I'm trying to set the value of a variable, given in each of the rows of that column. Say, I've got a table with the names of the tables I'll be working with:

DBNAME

Table1

Table2

Table3

Afterwards, I need to use these names to obtain the fields in each of the tables

Table1.field

Table2.field

Table3.field

I'd do it like this if it weren't 250 tables :S.

Thanks a lot!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ok, couple more steps back and in more detail please?

I'm trying to see if there's another way, because row number won't work the way you're using it. Need more details about the larger picture.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rugbyamok SSC Veteran Points: 281 More actions · Answer 4

Hi,

i corrected the loop and made an example of how it would work:

DECLARE @Count INT = 0

DECLARE @numTables INT = 10

DECLARE @tbl NVARCHAR(255)

WHILE @Count <= @NumTables

BEGIN

SELECT @tbl = RTRIM(tmp.[name]) FROM

(

SELECT t.name,

ROW_NUMBER() OVER(ORDER BY t.name) AS NUMROW

FROM sys.tables AS t WITH(NOLOCK)

) tmp WHERE NUMROW = @Count

SET @Count = @Count + 1

PRINT @tbl

END

This could be made in better ways (e.g. selecting your Row_Number into a temp-table first and then selecting the value), but this might do for you.

smorales 52862 Old Hand Points: 339 More actions · Answer 5

Hello,

there's not much more to say, honestly. Basically, I want to get the n-th row and use that value (the name of a DB) to work with its fields.

After that, all that's left is a comparison between the values in those fields and the fields from another DB.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

I can't see your screen, I haven't read your requirements, I can't read your mind 🙂

If you had no knowledge of this problem and someone told you just what is in this thread, could you figure it out?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

It looks like you are trying to run through sys.tables or whatever and do something to each table encountered, although as Gail points out, it's bl00dy hard to tell from your description! If you are, then why not use a cursor (fast-forward read-only, of course).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden