SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

JimB2
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.

afrika
Flowing Fount of Yak Knowledge

Nigeria
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

JimB2
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

khtan
In (Som, Ni, Yak)

Singapore
17681 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


----------------------------------
'KH'


Go to Top of Page

MichaelP
Jedi Yak

USA
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.

Michael

<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  
 New 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.05 seconds. Powered By: Snitz Forums 2000