數據庫分頁存儲過程，支持多表聯合查詢
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
