Using ROW_NUMBER function in SQL Server 2000

makumbi
 
on Jul 22, 2022 10:56 PM
399 Views

The code here is not supported in SQL Server 2000

Pplease help

SET @admno = (SELECT admno FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
SET @types = (SELECT stdtype FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
SET @Rdate = (select Dreturn FROM setdates2 where [class] like '%'+@c+'%' and studenttype=@types)

 

PROCEDURE [dbo].[UPdateDatereturn] @c nvarchar(50) AS 
BEGIN
    DECLARE @admno nvarchar(50)
    DECLARE @class nvarchar(50)
    DECLARE @types nvarchar(50)
    DECLARE @Rdate datetime
    
    DECLARE @Sum INT = 0
    DECLARE @Counter INT, @TotalCount INT
    
    SET @Counter = 1  
    SET @TotalCount = (SELECT COUNT(*) FROM temp)   
    WHILE (@Counter <= @TotalCount)
    BEGIN
        SET @admno = (SELECT admno FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
        SET @types = (SELECT stdtype FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) RowId,* FROM temp) t WHERE t.RowId = @Counter)
       SET @Rdate = (select Dreturn FROM setdates2 where [class] like '%'+@c+'%' and studenttype=@types)
       
        IF @types='DAY' 
        BEGIN           
            UPDATE P3P7 SET Returnsd =Convert (datetime,@Rdate,103)  WHERE admno = @admno
        END
       
	   ELSE IF @types='BOARDER'
        BEGIN
            UPDATE P3P7 SET Returnsd = Convert (datetime,@Rdate,103) WHERE admno = @admno
        END
     
        SET @Counter = @Counter + 1
        CONTINUE;
    END
END

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 23, 2022 01:37 AM

Hi makumbi,

You need to use IDENTITY function to replace the ROW_NUMBER function.

First you need to insert all the record to Temporary table with IDENTITY function for Row number.

Then use the Temporary table to get the column value inside the While loop.

Example

DECLARE @Customers AS TABLE(CustomerID INT, Name VARCHAR(50), Country VARCHAR(50))
INSERT INTO @Customers VALUES(1,'John Hammond','United States')
INSERT INTO @Customers VALUES(2,'Mudassar Khan','India')
INSERT INTO @Customers VALUES(3,'Suzanne Mathews','France')
INSERT INTO @Customers VALUES(4,'Robert Schidner','Russia')

SELECT IDENTITY(INT,1,1) RowId, CustomerID, Name, Country
INTO #Results
FROM @Customers

DECLARE @Id INT, @Name VARCHAR(50)
SET @Id = (SELECT CustomerID FROM #Results t WHERE t.RowId = 1)
SET @Name = (SELECT Name FROM #Results t WHERE t.RowId = 1)
SELECT @Id 'Id', @Name 'Name'

DROP TABLE #Results

Output

Id     Name

1     John Hammond