Community_Common_Count_USP
/**//*
作者: SLIGHTBOY
建立日期: 2005-8-17
修改日期:
存储作用: 通用记录统计
作用数据库: 自定义
输入变量:
@KeyColumn 主键字段名
@TableName 表格名称
@SearchQuery 查询条件 默认 ''
输出变量:
中间变量:
*/
CREATE PROCEDURE dbo.Community_Common_Count_USP
(
@KeyColumn varchar(20),
@TableName varchar(20),
@SearchQuery varchar(400) = ''
)
AS
SET NOCOUNT ON
DECLARE @Timer datetime
SET @Timer = getdate()
IF ( @SearchQuery IS Not NULL ) AND ( @SearchQuery <> '' )
exec('SELECT Count('+ @KeyColumn +') FROM ['+ @TableName +'] Where '+ @SearchQuery)
Else
exec('SELECT Count('+ @KeyColumn +') FROM ['+ @TableName +']')
print(DATEDIFF(millisecond, @Timer, getdate()))
GO
Community_Common_Page_USP
/**//*
作者: SLIGHTBOY
建立日期: 2005-8-12
修改日期:
存储作用: 通用分页存储过程
作用数据库: 自定义
版本: 1.0
输入变量:
@AbsolutePage 请求页 默认 1
@PageSize 每页记录数 默认 20
@RecordCount 总记录
@KeyColumn 主键字段名
@SelectColumn 查询字段
@TableName 表格名称
@Sort 排序方式 DESC
@SearchQuery 查询条件 默认 ''
@ExpandQuery 扩展内容(FOR XML EXPLICIT) 默认 ''
输出变量:
过程变量
@SqlQuery 查询语句
@AbsolutePosition 当前位置
*/
CREATE PROCEDURE dbo.Community_Common_Page_USP
(
@AbsolutePage int = 1,
@PageSize int = 20,
@RecordCount int,
@KeyColumn varchar(20),
@SelectColumn varchar(1000),
@TableName varchar(20),
@Sort varchar(4) = '',
@SearchQuery varchar(400) = '',
@ExpandQuery varchar(50) = ''
)
AS
SET NOCOUNT ON
DECLARE @Timer datetime
SET @Timer = getdate()
DECLARE @SqlQuery varchar(3000)
DECLARE @AbsolutePosition int
SET @AbsolutePosition = @AbsolutePage * @PageSize
IF ( @AbsolutePosition = @PageSize )
IF ( @Sort = 1 )
SET @SqlQuery = '
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +'
'+ @SelectColumn +'
FROM ['+ @TableName +']
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
ORDER BY '+ @KeyColumn +' ASC
'+ @ExpandQuery
Else
SET @SqlQuery = '
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +'
'+ @SelectColumn +'
FROM ['+ @TableName +']
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
ORDER BY '+ @KeyColumn +' DESC
'+ @ExpandQuery
-- 大于 首页 小于 中间页
Else IF ( @AbsolutePosition <= @RecordCount/2 )
IF ( @Sort = 1 )
SET @SqlQuery = '
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +'
FROM ['+ @TableName +']
WHERE '+ @KeyColumn +' >
(
SELECT MAX('+ @KeyColumn +')
FROM
(
SELECT TOP '+ LTRIM( STR( ( @AbsolutePage - 1)*@PageSize) ) +'
'+ @KeyColumn +'
FROM ['+ @TableName +']
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
ORDER BY '+ @KeyColumn +' ASC
) As Child
)
'+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +'
ORDER BY '+ @KeyColumn +' ASC
'+ @ExpandQuery
Else
SET @SqlQuery = '
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +'
FROM ['+ @TableName +']
WHERE '+ @KeyColumn +' <=
(
SELECT Min('+ @KeyColumn +')
FROM
(
SELECT TOP '+ LTRIM( STR( ( @AbsolutePage - 1)*@PageSize + 1 ) ) +'
'+ @KeyColumn +'
FROM ['+ @TableName +']
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
ORDER BY '+ @KeyColumn +' DESC
) As Child
)
'+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +'
ORDER BY '+ @KeyColumn +' DESC
'+ @ExpandQuery
Else IF (@AbsolutePosition > @RecordCount/2 )
IF ( @Sort = 1 )
SET @SqlQuery = '
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +'
FROM ['+ @TableName +']
WHERE '+ @KeyColumn +' >
(
SELECT Min('+ @KeyColumn +')
FROM
(
SELECT TOP '+ LTRIM( STR( @RecordCount - ((@AbsolutePage - 1) * @PageSize ) + 1) ) +'
'+ @KeyColumn +'
FROM ['+ @TableName +']
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
ORDER BY '+ @KeyColumn +' DESC
) As Child
)
'+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +'
ORDER BY '+ @KeyColumn +' ASC
'+ @ExpandQuery
Else
SET @SqlQuery = '
SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +'
FROM ['+ @TableName +']
WHERE '+ @KeyColumn +' <
(
SELECT MAX('+ @KeyColumn +')
FROM
(
SELECT TOP '+ LTRIM( STR( @RecordCount - ((@AbsolutePage - 1) * @PageSize ) + 1) ) +'
'+ @KeyColumn +'
FROM ['+ @TableName +']
'+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +'
ORDER BY '+ @KeyColumn +' ASC
) As Child
)
'+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +'
ORDER BY '+ @KeyColumn +' DESC
'+ @ExpandQuery
exec(@SqlQuery)
print(@SqlQuery)
print(DATEDIFF(millisecond, @Timer, getdate()))
GO
