SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--分页存储过程
CREATE PROCEDURE [dbo].[sp_Paging7]
(
@Tables nvarchar(1000), --表名/视图名
@pageindex int = 1, --当前页码
@PageSize int = 10, --每页记录数
@Filter1 nvarchar(1000) = NULL, --where过滤条件(不带where)
@Filter2 nvarchar(1000) = NULL, --where过滤条件(不带where)
@Filter3 nvarchar(1000) = NULL, --where过滤条件(不带where)
@time nvarchar(100) =N'time', --时间
@Sort nvarchar(200) = NULL, --排序字段(不带order by)
@Group nvarchar(1000) = NULL , --Group语句(不带Group By)
@Fields nvarchar(1000) = N'*' --输出字段
--@TotalCount int OUTPUT --总记录数
)
AS
DECLARE @SortTable nvarchar(100)
DECLARE @SortName nvarchar(100)
DECLARE @strSortColumn nvarchar(200)
DECLARE @operator char(2)
DECLARE @type nvarchar(100)
DECLARE @prec int
--设定排序语句
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @time
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
--设置排序字段类型和精度
SELECT @type=t.name, @prec=c.prec FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize nvarchar(50)
DECLARE @strStartRow nvarchar(50)
DECLARE @strFilter nvarchar(1000)
DECLARE @strSimpleFilter nvarchar(1000)
DECLARE @strGroup nvarchar(1000)
IF @pageindex <1
SET @pageindex = 1
SET @strPageSize = CAST(@PageSize AS nvarchar(50))
--设置开始分页记录数
SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50))
--筛选以及分组语句
IF @Filter3 IS NOT NULL AND @Filter3 != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter1 + ' ' + ' AND ' + @Filter2 + ' AND ' + @Filter3
SET @strSimpleFilter = ' AND ' + @Filter1 + ' ' + ' AND ' + @Filter2 + ' AND ' + @Filter3
END
ELSE
BEGIN
SET @strFilter = ' WHERE ' + @Filter1 + ' ' + ' AND ' + @Filter2
SET @strSimpleFilter = ' AND ' + @Filter1 + ' ' + ' AND ' + @Filter2
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY '
--计算总记录数
--DECLARE @TotalCountSql nvarchar(1000)
--SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilter
--EXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT
--执行查询语句
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
在网上查了资料,根据项目进行了修改,实现快速查询。