row_id in sql server

Hi all,
i have a task that whenever i will write a select query then when the result comes then i should be able to write a column that should write the ROW-ID of these rows. I mean for example i write a select query that gives me the following tablethe query can be like select bill_id,name from myTable order by name)
Bill_id name
25 abc
18 def
26 ghi
23 adfd

but my result should look like:
Row_Id Bill_id name
1 25 abc
2 18 def
3 26 ghi
4 23 adfd

n this Row_Id should be generated automatically....even if when i will write order by name in reverse order then also it should write the row_id as 1,2,3,4 same increasing order...n this automatic function i have to make...i cant understand..if there exist some procedure or function that already does this...or how can i just find the simple ROW_ID in general?????
any help will be greatly appreciated.
Regards.

This is a feature that has been added to SQL 2005.

In SQL 2000, there are two ways to fake it ...


(1) Insert your data into a temporary table with the IDENTITY(INT) function, like this:

SELECT IDENTITY(INT) AS RowId, MyTable.*
INTO #T
FROM MyTable

SELECT * FROM #T

DROP TABLE #T



(2) Use a TABLE variable -

DECLARE @WorkingTable TABLE (RowID INT IDENTITY(1,1), BillId INT, Name VARCHAR(10))

INSERT INTO @WorkingTable (BillId, Name)
SELECT BillId, Name
FROM MyTable
ORDER BY .....................

SELECT * FROM @WorkingTable



Nr 2 is ideal for a smaller number of rows.
Nr 1 allows you to add and use indexes if you need to JOIN the results, or filter.

oh THANK YOU SOOOOOOOO MUCH...!!!it works...<img src='/community/emoticons/emotion-1.gif' alt='' /><br />regards.

hi,
now regarding the same question..i have another problem...now i have to make a function in which the input parameter is a select statement i.e. any string and in that select statement i have to put a ROWID....that means i have to make a function that takes the select statement that has some table mentioned in it..n then i have to output the result as a table with rowid in it...how can i do that?????
regards.

Assuming you have the parameter @Command come in to your function/stored procedure. That would look like so: "select whatever from whatever where whatever order by whatever"

You could do the following:
select @Command = Replace(@Command, 'select', 'select identity(INT) RowId,')
select @Command = Replace(@Command, 'from', 'into #T from')
select @Command = @Command + ' select * from #T drop table #T'

The idea being that you simply replace the word select, with the phrase that you've already seen will end up yielding the row id, then you replace the word from with the part that will stick the results into your temp table, then you append the text that will return your results from the temp table which would be all of your requested results plus the row id.

Hope it helps

PS - If you have any problems with the "Select * from #t" or "drop table #t" you might need to add a carriage return line and line feed chr(13)+chr(10)

Hi,
Thanks a lot druer..i will try on monday if it works...n Madhivanan thanks for the suggestion n there r millions of data there...how to use record number...just curious to know...although i will try everythin on monday..
thanks once again to all,
Regards.

hi,i m not able to c the table data....i mean my procedure looks like this:<br />-- =============================================<br />ALTER PROCEDURE [dbo].[Get_Identity_Column]<br />( @p_select_command varchar(2)<br /> --@p_return_query TABLE output <br />)<br /> <br />AS<br />BEGIN <br />select @p_select_command = Replace(@p_select_command, 'select', 'select identity(INT) RowId,')<br />select @p_select_command = Replace(@p_select_command, 'from', 'into #TempTable from')<br /> --select @p_return_query = @p_select_command<br />select @p_select_command = @p_select_command + ' select * from #TempTable drop table #TempTable'<br /> exec @p_select_command<br />END<br />GO<br />-----------------------------------------------------<br />now how can i see the table...as TempTable does not exist...n i want that when i execute it then it should show me the result automatically...but it doesnt work like that <img src='/community/emoticons/emotion-6.gif' alt='' /> it just says that <br />---------------------<br />Msg 2812, Level 16, State 62, Procedure Get_Identity_Column, Line 22<br />Could not find stored procedure 'se'.<br /><br />(1 row(s) affected)<br />-----------------------------<br />how can i deal with this problem now????

oh thanks for pointin this mistake....i was just wondering y is it showing SE only again n again...but still it didnt solve my problem <img src='/community/emoticons/emotion-6.gif' alt='' /> ...now with the code:<br />--------------------<br />ALTER PROCEDURE [dbo].[Get_Identity_Column]<br />( @p_select_command varchar(2000)<br /> --@p_return_query TABLE output <br />)<br /> <br />AS<br />BEGIN <br />select @p_select_command = Replace(@p_select_command, 'select', 'select identity(INT) RowId,')<br />select @p_select_command = Replace(@p_select_command, 'from', ' into #TempTable from ')<br /> exec @p_select_command<br />select @p_select_command = @p_select_command + ' select * from #TempTable drop table #TempTable'<br /> --exec @p_select_command<br />END<br />GO<br />-----------------------------------<br />it gives me error as:<br />Msg 2812, Level 16, State 62, Procedure Get_Identity_Column, Line 22<br />Could not find stored procedure 'select identity(INT) RowId, bill.* into #TempTable from bill select * from #TempTable drop table #TempTable'.<br /><br />(1 row(s) affected)<br />------------------------------<br />now wat to do with this?????<br />while executing i m just writting <br />select bill.* from bill....<br />waitin for your reply...<br />regards.

i just want to show the data only on the executed screen....i mean the QUERY WINDOW...just when i will execute:
----------------------
USE [Telbill]
GO

DECLARE@return_value int

EXEC@return_value = [dbo].[Get_Identity_Column]
@p_select_command = N'select bill.* from bill'

SELECT'Return Value' = @return_value

GO
------------------------
then just after pressing EXECUTE...it should show me the tables...
regards.

i dont want to export result...the purpose is just to check the functionality of my procedure...if it works fine or not...if its showing me the correct selected table or not...n its not a front end application...its just a normal procedure that takes as the input a select command and shows the result with an identity column...thats all...

Keep a straight line on what stuff you're passing to-and-from any procedure, and how you're doing it. To return a value from an SP, you're better off using the OUTPUT keyword. You need to add it to the parameter definition and on the line where you call the procedure.

Last point: you still had an active call to exec @p_select_command in your SP body, that's what was throwing the error.

CREATE PROCEDURE [dbo].[Get_Identity_Column]
( @p_select_command varchar(2000) OUTPUT)

AS
BEGIN
select @p_select_command = Replace(@p_select_command, 'select', 'select identity(INT) RowId,')
select @p_select_command = Replace(@p_select_command, 'from', ' into #TempTable from ')
select @p_select_command = @p_select_command + ' select * from #TempTable drop table #TempTable'
END
GO

DECLARE @return_value varchar(2000)

SET @return_value = N'select bill.* from bill'

EXEC [dbo].[Get_Identity_Column] @return_value OUTPUT

SELECT @return_value

GO

DROP PROCEDURE [dbo].[Get_Identity_Column]
GO

Thank you very much Madhivanan and Adriaan....that was a lot of help...thanks a lot...it worked finally [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br />Regards.

The easiest solution to all your problem is to customise the query below and your problem is solved
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by rank