数据库分页存储进程，支撑多表结合查询
CREATE PROCEDURE [dbo].[getInQuiryAllByPage]
@tableNames varchar(5000), --表名,可多表,逗号分隔
@tbFields varchar(5000)= "* ",--字段名,如果多表请带前缀
@conditionStr varchar(5000)= " ", --where子句,可为空,不带where
@sortedStr varchar(5000), --排序字段,可多个,要带desc或asc,不带order by,必需,不能为空
--排序字段不能有空值,或在where中消除空值或用isnull函数解决
@needCount bit = 1, --是不是须要得到纪录总数
@pageIndex int =0, --页索引
@pageSize int=10, --页大小
@recordCount int =0 output, --返回纪录总数
@pageCount int =0 output --返回页总数
AS
BEGIN
declare @sql nvarchar(4000) --主sql语句
declare @sortStr2 varchar(8000) --order by子句
declare @sortStr3 varchar(8000) --order by子句
declare @whereStr nvarchar(4000) --条件
declare @sortStr nvarchar(4000) --条件
set @sortStr = LOWER(@sortedStr)
set @sortStr2 = REPLACE(@sortStr, "desc", " @a@ ")
set @sortStr2 = REPLACE(@sortStr2, "asc", " @d@ ")
set @sortStr2 = REPLACE(@sortStr2, " @a@ ", " asc ")
set @sortStr2 = REPLACE(@sortStr2, " @d@ ", " desc ")
--------------------------------------------------------------
set @sortStr2 = REPLACE(@sortStr2,"a."," ")
set @sortStr2 = REPLACE(@sortStr2,"b."," ")
set @sortStr2 = REPLACE(@sortStr2,"c."," ")
set @sortStr2 = REPLACE(@sortStr2,"e."," ")
set @sortStr2 = REPLACE(@sortStr2,"f."," ")
set @sortStr2 = REPLACE(@sortStr2,"g."," ")
set @sortStr3 = @sortStr
set @sortStr3 = REPLACE(@sortStr3,"a."," ")
set @sortStr3 = REPLACE(@sortStr3,"b."," ")
set @sortStr3 = REPLACE(@sortStr3,"c."," ")
set @sortStr3 = REPLACE(@sortStr3,"e."," ")
set @sortStr3 = REPLACE(@sortStr3,"f."," ")
set @sortStr3 = REPLACE(@sortStr3,"g."," ")
--------------------------------------------------------------
set @sortStr = " order by " + @sortStr
set @sortStr2 = " order by " + @sortStr2
set @sortStr3 = " order by " + @sortStr3
if(@conditionStr is not null and @conditionStr != " " )
set @whereStr = " where " + @conditionStr
else
set @whereStr = " "
--if(@needCound != 0 or @pageIndex = 0 ) --以下取得纪录总数
begin
DECLARE @R int
SET @sql= "select @R=count(*) from "+@tableNames + @whereStr
EXEC SP_EXECUTESQL @SQL,N"@R int OUTPUT ",@R OUTPUT
SET @recordCount = @R
set @pageCount = ((@recordCount-1)/@pageSize)+1
end
if(@pageIndex <2) --如果是第一页
begin
set @pageIndex = 1
set @sql= "select top "+ str(@pageSize)
+ " "+ @tbFields + " from " + @tableNames
+ @whereStr + @sortStr;
end
else --其它页
begin
if( @recordCount>@pageIndex*@pageSize)
SET @sql= "SELECT * FROM ( "
+ "SELECT TOP " + STR(@pageSize) + " * FROM ( "
+ "select top " + STR(@pageSize*@pageIndex) + " "+ @tbFields + " FROM "
+ @tableNames + @whereStr + @sortStr + ") as a "
+ @sortStr2 + ") as b " + @sortStr3
else
SET @sql= " select * FROM ("
+ " SELECT top "+ STR(@recordCount-@pageSize*(@pageCount-1))
+ " "+ @tbFields + " FROM "
+ @tableNames + @whereStr + @sortStr2 + " ) AS a"
+ @sortStr3
end
print @sql
EXEC SP_EXECUTESQL @sql
END
CREATE PROCEDURE [dbo].[getInQ
