USE [xxxxx]
GO
/****** Object: StoredProcedure [dbo].[PROC_DataPaging] Script Date: 07/12/2012 14:23:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[PROC_DataPaging]
@tbName nvarchar(255), --表名
@tbFields nvarchar(1023), --返回字段
@OrderField nvarchar(255), --排序的字段名
@PageSize int, --页尺寸
@PageIndex int, --页码
@OrderType bit, --排序类型,1是升序,0是降序
@strWHERE nvarchar(1023), --查询条件
@Total int output --返回总记录数
AS
DECLARE @strSql nvarchar(1023) --主语句
DECLARE @strOrder nvarchar(255) --排序
DECLARE @strSqlCount nvarchar(511) --查询记录总数主语句
DECLARE @strtemp nvarchar(63) --排序附加语句
--------------排序--------------------1是降序,0未升序
IF @OrderType != 0
BEGIN
SET @strtemp = '>(SELECT MAX('
SET @strOrder = 'ORDER BY ' + @OrderField + ' ASC '
END
ELSE
BEGIN
SET @strtemp = '<(SELECT MIN('
SET @strOrder = 'ORDER BY '+ @OrderField + ' DESC'
END
--------------总记录数---------------
IF @strWHERE !=''
BEGIN
SET @strSqlCount = 'SELECT @TotalCout=count(*) FROM ' + @tbName + ' WHERE ' + @strWHERE
END
ELSE
BEGIN
SET @strSqlCount = 'SELECT @TotalCout=count(*) FROM ' + @tbName
END
--------------如果是第一页------------
IF @PageIndex = 1
BEGIN
IF @strWHERE != ''
BEGIN
SET @strSql = 'SELECT TOP' + str(@PageSize)+ ' ' + @tbFields + ' FROM ' + @tbName +' WHERE ' + @strWHERE + ' ' + @strOrder
END
ELSE
BEGIN
SET @strSql = 'SELECT TOP' + str(@PageSize)+ ' ' + @tbFields + ' FROM ' + @tbName + ' ' + @strOrder
END
END
------------第一页之外----------------
ELSE
BEGIN
IF @strWHERE != ''
BEGIN
SET @strSql = 'SELECT TOP ' + str(@PageSize) + ' ' + @tbFields
+ ' FROM ' + @tbName + ' WHERE '+ @OrderField +' '
+ @strtemp + ' ' + @OrderField + ') FROM (SELECT TOP'
+ str((@PageIndex-1)*@PageSize) + ' ' + @OrderField + ' FROM ' + @tbName + ' WHERE '
+ @strWHERE + ' ' + @strOrder +') AS tb) AND '+ @strWHERE + ' ' + @strOrder
END
ELSE
BEGIN
SET @strSql = 'SELECT TOP ' + str(@PageSize) + ' ' + @tbFields
+ ' FROM ' + @tbName + ' WHERE ' + @OrderField + ' '
+ @strtemp+ ' ' +@OrderField+ ') FROM (SELECT TOP'
+ str((@PageIndex-1)*@PageSize) + ' '+ @OrderField +' FROM ' + @tbName
+ ' '+ @strOrder +') AS tb)'+ @strOrder
END
END
exec sp_executesql @strSqlCount, N'@TotalCout int output',@Total output
exec(@strSql)
SELECT @strSql AS StrSQL
SELECT @strSqlCount AS strSqlCount
GO