你的位置:首页 > 数据库

[数据库]SQL存储过程分页(通用的拼接SQL语句思路实现)


多表通用的SQL存储过程分页

 

USE [EmailCenter]GO/****** Object: StoredProcedure [dbo].[Common_PageList]  Script Date: 2016/2/29 11:00:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROC [dbo].[Common_PageList](@tab nvarchar(200),---表名@strFld nvarchar(max), --字段字符串@strWhere varchar(max), --where条件 @PageIndex int, --页码@PageSize int, --每页容纳的记录数@Sort VARCHAR(255), --排序字段及规则,不用加order by@Total int output)ASdeclare @strSql nvarchar(max)set nocount on;set @strSql=' SELECT * FROM (SELECT ROW_NUMBER()  OVER(ORDER BY ' + @Sort + ') AS rownum, ' + @strFld + ' FROM ' + @tab + ' where 1=1 ' + @strWhere + ') AS Dwhere WHERE rownum BETWEEN ' + CAST(((@PageIndex-1)*@PageSize + 1) as nvarchar(20)) + ' and ' + cast((@PageIndex*@PageSize) as nvarchar(20)) exec (@strSql) set @strSql='SELECT @Total = COUNT(0) FROM ' + @tab + ' WHERE 1=1 ' + @strWhere EXECUTE sp_executesql @strSql,N'@Total INT OUTPUT',@Total OUTPUTset nocount off;GO