02 |
set QUOTED_IDENTIFIER ON
|
07 |
如果有自增标识字段,在@strGetFields中不要加入此字 段信息, |
08 |
如果非要加入的话,要 (fldName + 0) AS fldName 这样处理;
|
11 |
@strGetFields: 需要返回的列 '*' :返回所以列信息
|
14 |
@doCount: 返回记录总数, 非 0 值则返回 |
15 |
@strOrderBy: 排序字段信息,(注意: 不要加 ORDER BY )
|
16 |
格式: Field1 DESC , Field2 ASC
|
17 |
@strWhere: 查询条件,(注意: 不要加 WHERE )
|
18 |
输出参数: @RecordCount: 记录总数 |
23 |
ALTER PROCEDURE [dbo].[MyPagination]
|
25 |
@tblName varchar (255),
|
26 |
@strGetFields varchar (1000) = '*' ,
|
30 |
@strOrderBy varchar (500) = '' ,
|
31 |
@strWhere varchar (1500) = '' ,
|
32 |
@RecordCount int output
|
36 |
DECLARE @strSQL varchar (5000) SET @strSQL = ''
|
38 |
DECLARE @strOrder varchar (400) SET @strOrder = ''
|
41 |
--如果@doCount传递过来的不是0,就执行总数统计 |
44 |
DECLARE @sWhere varchar (2000)
|
48 |
SET @sWhere = ' WHERE ' + @strWhere
|
50 |
SET @strSQL = 'if exists (select * from dbo.sysobjects where id = object_id(' '[dbo].[tmpTable]' ') and OBJECTPROPERTY(id, ' 'IsUserTable' ') = 1) '
|
51 |
SET @strSQL = @strSQL + ' UPDATE tmpTable SET Total = (SELECT COUNT(*) FROM [' + @tblName + '] ' + @sWhere + ') '
|
52 |
SET @strSQL = @strSQL + ' ELSE SELECT COUNT(*) AS Total INTO tmpTable FROM [' + @tblName + '] ' + @sWhere
|
56 |
SELECT @RecordCount=Total FROM tmpTable
|
59 |
EXEC ( 'DROP TABLE tmpTable' )
|
65 |
IF (@strOrderBy != '' )
|
66 |
SET @strOrder = ' ORDER BY ' + @strOrderBy
|
67 |
--如果是第一页就执行以上代码,这样会加快执行速度 |
71 |
SET @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @strGetFields + ' FROM [' + @tblName + '] WHERE ' + @strWhere + @strOrder
|
73 |
SET @strSQL = 'SELECT TOP ' + str(@PageSize) + ' ' + @strGetFields + ' FROM [' + @tblName + '] ' + @strOrder
|
78 |
SET @strSQL = 'SELECT TOP ' + str(@PageIndex*@PageSize) + ' IDENTITY(int,1,1) AS IID, ' + @strGetFields + ' INTO #tmpTable FROM [' + @tblName + ']'
|
80 |
SET @strSQL = @strSQL + ' WHERE ' + @strWhere + @strOrder
|
82 |
SET @strSQL = @strSQL + @strOrder
|
84 |
--以下代码赋予了@strSQL以真正执行的SQL代码 |
85 |
SET @strSQL = @strSQL + ' SELECT ' + @strGetFields + ' FROM #tmpTable WHERE IID > ' + str((@PageIndex-1)*@PageSize) + ' DROP TABLE #tmpTable'
|
本文转自today4king博客园博客,原文链接:http://www.cnblogs.com/jinzhao/archive/2010/03/09/1681772.html,如需转载请自行联系原作者