程序sql server中的分页 [英] Pagination in procedure sql server

问题描述

同样的程序在Mysql中工作，将@ RowId + 1作为RowId放在@RowID：= @RowID + 1，因为RowId可能我知道是什么问题。



ALTER PROCEDURE [dbo]。[uspGetCitysList]

（

@PageSize TINYINT，

@PageIndex TINYINT

）

AS

BEGIN

- 添加SET NOCOUNT ON以防止额外的结果集

- - 干扰SELECT语句。

SET NOCOUNT ON;



DECLARE @RowStart TINYINT

DECLARE @RowEnd TINYINT

DECLARE @RowId TINYINT



SET @RowStart = @ PageSize * @PageIndex +1

SET @RowEnd = @RowStart + @PageSize -1

SET @RowId = 0



SELECT CityId，Name，EntryBy，Username，EntryDate，StatusId < br $>


FROM

（

SELECT @ RowId + 1作为RowId，CityId，Name，EntryBy，U.Username ，EntryDate，C.StatusId



FROM tblCitys C


INNER JOIN tblUsers U



ON C.EntryBy = U.UserId



在哪里C.StatusId！= 3

）在@RowStart和@RowEnd之间的行间距离



SELECT COUNT（*）作为RowsCount来自tblCitys WHERE StatusId！= 3



END

The same procedure is working in Mysql by placing @RowId+1 as RowId with @RowID := @RowID + 1 as RowId may i know what is the problem.

ALTER PROCEDURE [dbo].[uspGetCitysList]
(
@PageSize TINYINT,
@PageIndex TINYINT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @RowStart TINYINT
DECLARE @RowEnd TINYINT
DECLARE @RowId TINYINT

SET @RowStart = @PageSize * @PageIndex +1
SET @RowEnd = @RowStart + @PageSize -1
SET @RowId = 0

SELECT CityId, Name, EntryBy, Username, EntryDate, StatusId

FROM
(
SELECT @RowId+1 as RowId, CityId, Name, EntryBy,U.Username, EntryDate, C.StatusId

FROM tblCitys C

INNER JOIN tblUsers U

ON C.EntryBy = U.UserId

WHERE C.StatusId != 3
) A WHERE RowId BETWEEN @RowStart AND @RowEnd

SELECT COUNT(*) as RowsCount from tblCitys WHERE StatusId != 3

END

推荐答案

Insted of这个



SELECT @ RowId + 1作为RowId，



i使用了这个



ROW_NUMBER（）超过（由CityId订购）为RowId，

Insted of this

SELECT @RowId+1 as RowId,

i have used this

ROW_NUMBER() OVER (ORDER BY CityId) as RowId,

这篇关于程序sql server中的分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！