Question

关于sql server中存储过程的一个问题

ALTER PROCEDURE P_GetPagedOrders2005
(@startIndex INT,
@pageSize INT
)
AS
begin
WITH orderList AS (
SELECT ROW_NUMBER() OVER (ORDER BY O.orderid DESC)AS Row, O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID)

SELECT orderid,orderdate,customerid,companyName,employeeName
FROM orderlist
WHERE Row between @startIndex and @startIndex+@pageSize-1

end

以上是sql server2005中的一个存储过程，而sql2000里面没有ROW_NUMBER()这个函数，请问：如果这段存储过程应该怎么改才能在sql2000里执行？谢谢了（如果回答正确我会追加分的）

Answer 1

row_number()函数是返回按某一数字列排序后的行号。如果在sqlserver 2000下运行可以利用identity函数，导出临时表(如果数据量很大，最好不要这么去做，想别的方法吧)，如下

ALTER PROCEDURE P_GetPagedOrders2005
(@startIndex INT,
@pageSize INT
)
AS

if exists(select * from sysobjects where name='tabletemp') drop table tabletemp

SELECT identity(int,1,1) as row, O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
into tabletemp
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID
order by o.orderid

SELECT orderid,orderdate,customerid,companyName,employeeName
FROM tabletemp
WHERE Row between @startIndex and @startIndex+@pageSize-1

drop table tabletemp

Answer 2

没有用过2005
推测ROW_NUMBER（）应该是计算表的数据行数吧
先写个存储过程实现这个函数，然后在执行这个存储过程
网上搜来的一段计算所有表行数的存储过程，参考一下吧

declare @tbl nvarchar(50)

Create table #tblSpace(
Name nvarchar(40),
Rows int,
reserved varchar(18),
Data varchar(18),
index_size varchar(18),
Unused varchar(18)
)

declare cur cursor for
Select name from sysobjects where xtype='U'
open cur
Fetch next from cur into @tbl
while @@fetch_status=0
begin
Insert into #tblSpace
EXEC sp_spaceused @tbl

Fetch next from cur into @tbl
end
close cur
deallocate cur
go

select name as 表名,rows as 行数 from #tblSpace
drop table #tblSpace

Answer 3

row_number()函数是返回按某一数字列排序后的行号。如果在sqlserver 2000下运行可以利用identity函数，导出临时表(如果数据量很大，最好不要这么去做，想别的方法吧)