470,648 Members | 1,459 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Getting rownumber in View in SQL2000

Yes In SQL2000 , Row_Number() function will not work as in SQL2005.
But, we can use the IDENTITY(INT,1,1) function but it requires a temporary table to which it stores the data and in View does'nt Support Temporary tables.

what I did is, I created a function which returns a table and in that table I have but 1 Identity field with AutoIncrement...
Sample Code
****************
ALTER FUNCTION dbo.UF_GetName()

RETURNS @Temp_tbl TABLE (
[RowNumber] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
AS
BEGIN

INSERT INTO @Temp_tbl (Name)
SELECT
Name
FROM
Users
WHERE
Name like 'a%'

ORDER BY Name


RETURN

END

GO

************************************************** ***************************

Now, you can use this function in the stored procedure or views and you want to ... and the RowNumber in the table will give you the RowNumbers for ur query Result...

SampleCode
****************

Create View View_GetName
as
SELECT TOP 100 PERCENT RowNumber,Name FROM dbo.UF_GetName()
WHERE RowNumber BETWEEN 1 AND 100

********************

then Execute the View u will get the result...


I Hope this post might help someone, who is trying to create view in sql2000 and wants the rownumber in the result.

Cheers !!!
Vineet CS
Oct 29 '09 #1
0 4122

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by John Hunter | last post: by
4 posts views Thread by Richard Holliingsworth | last post: by
6 posts views Thread by vbmark | last post: by
2 posts views Thread by PointMan | last post: by
2 posts views Thread by =?Utf-8?B?TWFyYw==?= | last post: by
1 post views Thread by Korara | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.