Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Number the rows in a record set
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 02/03/2006 :  14:33:39  Show Profile  Reply with Quote
I want to number the rows in a record set as it is created. This is an ordered set and I would use the row number in a treeview display.

Flowing Fount of Yak Knowledge

2706 Posts

Posted - 02/03/2006 :  14:44:15  Show Profile  Reply with Quote
Welcome to SQLTeam,

would advice, Lets see your table structure

You can use the auto increment integer value. Is this what you want ?

Edited by - afrika on 02/03/2006 14:45:56
Go to Top of Page

Starting Member

2 Posts

Posted - 02/03/2006 :  17:01:32  Show Profile  Reply with Quote
I want to create a record set to use in a treeview on a web page, the data is based on some SQL view. I want to search for an employee name with partial information. Perhaps the first name is guessed from one or two choices - Bill or William and the last name starts with T or Th. Or I want to find an office by name that I don't remember fully. Look ups could be done; then go to a tree structure to see the surrounding office/personnel structure of the selected person or office. Someone created a temp table for the record set and added an integer field and called it counter1, then did an update to that column. See the code:
DECLARE @counter int
SET @counter = 0
UPDATE #tmp_Users
SET @counter = counter = @counter + 1

This may work.....
Go to Top of Page

In (Som, Ni, Yak)

17689 Posts

Posted - 02/03/2006 :  18:15:32  Show Profile  Reply with Quote
create the temp table with int identity and insert your result set into that temp table and then select back from the temp table

create table #temp
    row_number int identity(1,1),
    col1 ...
    col2 ...

insert into #temp(col1, col2, ...)
    select ....
    from   ....
    where  ....
    order by ...

select * from #temp order by row_number


Go to Top of Page

Jedi Yak

2489 Posts

Posted - 02/03/2006 :  18:44:29  Show Profile  Visit MichaelP's Homepage  Reply with Quote
You could use @@RowCount and put that into an output parameter in your stored proc.

Do a search in the Books Online for @@rowcount.


<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000