CREATE PROC [dbo].[proc_select_id]
ELSE
IF(@pageindex=1)
--BEGIN
BEGIN
EXEC(@sql)

DECLARE @sql NVARCHAR(4000)
@orderstr VARCHAR(500),--排序条件
SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
GO
--根据TOP ID
CREATE PROC [dbo].[proc_select_page_top]
EXEC sp_executesql @sql,N'@totalRecord bigint OUTPUT',@totalRecord OUTPUT
@tablename VARCHAR(50)='',--表名
BEGIN
--IF(@totalRecord IS NULL)
SET @operatestr='MAX'
EXEC(@sql)
+N'(SELECT '+@operatestr+N'('+@keyid+N') FROM '+@tablename+N' WHERE '+@keyid
BEGIN

SET @sql=N'SELECT @totalRecord=COUNT(*)'
AS
DECLARE @operatestr CHAR(3),@comparestr CHAR(1)
@pageindex INT=1,--当前页数
IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
+N' FROM '+@tablename
IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
--IF(@totalRecord IS NULL)
GO
CREATE PROC [dbo].[proc_select_page_row]
@pageindex INT=1,--当前页数
@keyid VARCHAR(50)='',--主键
+N' FROM '+@tablename
@condition NVARCHAR(1000)='',--查询条件
IF(@pageindex=1)
@fields VARCHAR(1000)='',--查询的字段集合
@orderstr VARCHAR(500),--排序条件
DECLARE @sql NVARCHAR(4000)
@pageindex int=1,--当前页数
BEGIN
@pagesize INT=10,--每页大小
--END
IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
EXEC(@sql)
END
SET @comparestr='<'
-- BEGIN

@keyid VARCHAR(50)='',--主键
+N' WHERE '+@condition
SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@keyid
END
@pagesize INT=10,--每页大小

SET @comparestr='>'
@fields VARCHAR(1000)='*',--查询的字段集合
AS
IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
@pagesize int=10,--每页大小
EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT
--BEGIN
IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
ELSE
@orderstr VARCHAR(500),--排序条件

BEGIN
--END
BEGIN
@totalRecord BIGINT OUTPUT--总记录数
--根据MAX(MIN)ID
IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
SET @sql=N'SELECT top '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@keyid+@comparestr
IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
@totalRecord BIGINT OUTPUT--总记录数
+N' IN (SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '+@tablename+N' WHERE '
BEGIN
END
@condition NVARCHAR(1000)='',--查询条件
END
IF(@orderstr<>'')
EXEC(@sql)
+N' NOT IN(SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '
@condition NVARCHAR(1000)='',--查询条件

复制代码 代码如下:

SET @StartRecord = (@pageindex-1)*@pagesize + 1
END
ELSE
IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
@fields VARCHAR(1000)='',--查询的字段集合
SET @sql=N'SELECT * FROM (SELECT ROW_NUMBER() OVER ('+ @orderstr +N') AS rowId,'+@fields+N' FROM '+ @tablename+N') AS T WHERE rowId>='+STR(@StartRecord)+N' and rowId<='+STR(@StartRecord + @pagesize - 1)
+N' WHERE '+@condition
IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
IF(@pageindex=1)
EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT
DECLARE @StartRecord INT
@keyid VARCHAR(50)='',--主键

-- IF @totalRecord IS NULL
@tablename VARCHAR(50)='',--表名
BEGIN
SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
EXEC(@sql)
SET @sql=N'SELECT @totalRecord=COUNT(*)'
SET @operatestr='MIN'
@totalRecord BIGINT OUTPUT--总记录数
@tablename VARCHAR(50)='',--表名
GO
SET @sql=N'SELECT @totalRecord=COUNT(*)'
+@condition+N' '+@orderstr+N')) AND '+@condition+N' '+@orderstr
SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
+N' WHERE '+@condition
IF(CHARINDEX('desc',LOWER(@orderstr))<>0)
END
+N' FROM '+@tablename
DECLARE @sql NVARCHAR(4000)
--END
END
+@tablename+N' WHERE '+@condition+N' '+@orderstr+N') AND '+@condition+N' '+@orderstr
EXEC(@sql)
--根据ROW_NUMBER() OVER
END
AS