set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--分页存储过程
CREATE PROCEDURE [dbo].[sp_Paging]
(
@Tables nvarchar(1000), --表名/视图名
@PrimaryKey nvarchar(100), --主键
@Sort nvarchar(200) = NULL, --排序字段(不带order by)
@pageindex int = 1, --当前页码
@PageSize int = 10, --每页记录数
@Fields nvarchar(1000) = N'*', --输出字段
@Filter nvarchar(1000) = NULL, --where过滤条件(不带where)
@Group nvarchar(1000) = NULL --Group语句(不带Group By)
)
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 = @PrimaryKey
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 @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
SET @strGroup='';
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY '+@Group;
/*
--计算总记录数
DECLARE @TotalCountSql nvarchar(1000)
SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilter
EXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT
*/
--执行查询语句
declare @sql varchar(2000);
declare @sql2 varchar(1000);
declare @sql3 varchar(1000);
declare @sql4 varchar(1000);
declare @sql5 varchar(1000);
set @sql = 'DECLARE @SortColumn ' + @type+';';
set @sql2 = 'SET ROWCOUNT ' + @strStartRow+';';
/*
print '@strSortColumn='+@strSortColumn;
print '@Tables:'+@Tables;
print '@strFilter:'+@strFilter;
print '@strGroup:';
print '@Sort:'+@Sort;
*/
set @sql3='SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ORDER BY ' + @Sort+';';
set @sql4='SET ROWCOUNT ' + @strPageSize+';';
set @sql5='SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ORDER BY ' + @Sort + ''+';';
/*
print 'sql:'+@sql;
print 'sql2:'+@sql2;
print 'sql3:'+@sql3;
print 'sql4:'+@sql4;
print 'sql5:'+@sql5;
*/
set @sql = @sql+@sql2+@sql3+@sql4+@sql5;
print @sql;
EXEC(@sql)