【www.bbyears.com--Mssql】
--根据MAX(MIN)ID
代码如下 CREATE PROC [dbo].[proc_select_id]@pageindex int=1,--当前页数
@pagesize int=10,--每页大小
@tablename VARCHAR(50)="",--表名
@fields VARCHAR(1000)="",--查询的字段集合
@keyid VARCHAR(50)="",--主键
@condition NVARCHAR(1000)="",--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
IF ISNULL(@orderstr,N"")=N"" SET @orderstr=N" ORDER BY "+@keyid+N" DESC "
IF ISNULL(@fields,N"")=N"" SET @fields=N"*"
IF ISNULL(@condition,N"")=N"" SET @condition=N"1=1"
DECLARE @sql NVARCHAR(4000)
--IF(@totalRecord IS NULL)
--BEGIN
SET @sql=N"SELECT @totalRecord=COUNT(*)"
+N" FROM "+@tablename
+N" WHERE "+@condition
EXEC sp_executesql @sql,N"@totalRecord INT OUTPUT",@totalRecord OUTPUT
--END
IF(@pageindex=1)
BEGIN
SET @sql=N"SELECT TOP "+STR(@pagesize)+N" "+@fields+N" FROM "+@tablename+N" WHERE "+@condition+N" "+@orderstr
EXEC(@sql)
END
ELSE
BEGIN
DECLARE @operatestr CHAR(3),@comparestr CHAR(1)
SET @operatestr="MAX"
SET @comparestr=">"
IF(@orderstr<>"")
BEGIN
IF(CHARINDEX("desc",LOWER(@orderstr))<>0)
BEGIN
SET @operatestr="MIN"
SET @comparestr="<"
END
END
SET @sql=N"SELECT top "+STR(@pagesize)+N" "+@fields+N" FROM "+@tablename+N" WHERE "+@keyid+@comparestr
+N"(SELECT "+@operatestr+N"("+@keyid+N") FROM "+@tablename+N" WHERE "+@keyid
+N" IN (SELECT TOP "+STR((@pageindex-1)*@pagesize)+N" "+@keyid+N" FROM "+@tablename+N" WHERE "
+@condition+N" "+@orderstr+N")) AND "+@condition+N" "+@orderstr
EXEC(@sql)
END
GO
--根据ROW_NUMBER() OVER
@pageindex INT=1,--当前页数
@pagesize INT=10,--每页大小
@tablename VARCHAR(50)="",--表名
@fields VARCHAR(1000)="*",--查询的字段集合
@keyid VARCHAR(50)="",--主键
@condition NVARCHAR(1000)="",--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
IF ISNULL(@orderstr,N"")=N"" SET @orderstr=N" ORDER BY "+@keyid+N" DESC "
IF ISNULL(@fields,N"")=N"" SET @fields=N"*"
IF ISNULL(@condition,N"")=N"" SET @condition=N"1=1"
DECLARE @sql NVARCHAR(4000)
-- IF @totalRecord IS NULL
-- BEGIN
SET @sql=N"SELECT @totalRecord=COUNT(*)"
+N" FROM "+@tablename
+N" WHERE "+@condition
EXEC sp_executesql @sql,N"@totalRecord bigint OUTPUT",@totalRecord OUTPUT
--END
IF(@pageindex=1)
BEGIN
SET @sql=N"SELECT TOP "+STR(@pagesize)+N" "+@fields+N" FROM "+@tablename+N" WHERE "+@condition+N" "+@orderstr
EXEC(@sql)
END
ELSE
BEGIN
DECLARE @StartRecord INT
SET @StartRecord = (@pageindex-1)*@pagesize + 1
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)
EXEC(@sql)
END
GO
--根据TOP ID
@pageindex INT=1,--当前页数
@pagesize INT=10,--每页大小
@tablename VARCHAR(50)="",--表名
@fields VARCHAR(1000)="",--查询的字段集合
@keyid VARCHAR(50)="",--主键
@condition NVARCHAR(1000)="",--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
IF ISNULL(@orderstr,N"")=N"" SET @orderstr=N" ORDER BY "+@keyid+N" DESC "
IF ISNULL(@fields,N"")=N"" SET @fields=N"*"
IF ISNULL(@condition,N"")=N"" SET @condition=N"1=1"
DECLARE @sql NVARCHAR(4000)
--IF(@totalRecord IS NULL)
--BEGIN
SET @sql=N"SELECT @totalRecord=COUNT(*)"
+N" FROM "+@tablename
+N" WHERE "+@condition
EXEC sp_executesql @sql,N"@totalRecord INT OUTPUT",@totalRecord OUTPUT
--END
IF(@pageindex=1)
BEGIN
SET @sql=N"SELECT TOP "+STR(@pagesize)+N" "+@fields+N" FROM "+@tablename+N" WHERE "+@condition+N" "+@orderstr
EXEC(@sql)
END
ELSE
BEGIN
SET @sql=N"SELECT TOP "+STR(@pagesize)+N" "+@fields+N" FROM "+@tablename+N" WHERE "+@keyid
+N" NOT IN(SELECT TOP "+STR((@pageindex-1)*@pagesize)+N" "+@keyid+N" FROM "
+@tablename+N" WHERE "+@condition+N" "+@orderstr+N") AND "+@condition+N" "+@orderstr
EXEC(@sql)
END
GO