MS-SQL分页函数+A方法_MySQL数据库_黑客防线网安服务器维护基地--Powered by WWW.RONGSEN.COM.CN

MS-SQL 分页函数+A

作者:黑客防线网安MYSQL维护基地 来源:黑客防线网安MYSQL维护基地 浏览次数:0

本篇关键词:SQL数据库
黑客防线网安网讯:     CREATE PROCEDURE [dbo].[USP_GetList] ( @pageIndex int = 1 -- 当前页码0 表示返回所有符合内容 ,@pageSize int = 10 -- 页尺寸 ,@SID nvarchar(30) = '' -- 主键字段 ,@str...

     CREATE PROCEDURE [dbo].[USP_GetList]
(
@pageIndex int = 1 -- 当前页码0 表示返回所有符合内容
,@pageSize int = 10 -- 页尺寸
,@SID nvarchar(30) = '' -- 主键字段
,@strGetField nvarchar(1000) = '*' -- 需要返回的列
,@strTableName nvarchar(30) = '' --表名
,@strWhere nvarchar(2000) = '' -- 查询条件(注意: 条件中要带where)
,@strOrderBy nvarchar(300) = '' -- 排序
--,@MemberID int=0 --会员ID
)
AS
SET NOCOUNT ON

DECLARE @strSQL nvarchar(4000)
DECLARE @startPos int
DECLARE @endPos int

DECLARE @num int

SET @startPos=@pageSize*(@pageIndex-1)+1
SET @endPos=@startPos+@pageSize-1

--页大小*(页数-1)
SET @num = @pageSize * (@PageIndex - 1)

IF @PageIndex!=0
BEGIN
IF @strWhere != ''
SET @strSQL = 'SELECT TOP '+ cast(@pageSize as nvarchar(10)) +' '+ @strGetField +' FROM '+ @strTableName +' WHERE ('+ cast(@SID as nvarchar(30)) +' NOT IN (SELECT TOP '+ cast(@num as nvarchar(20)) +' '+ cast(@SID as nvarchar(30)) +' FROM '+ @strTableName +' where '+@strWhere+' ORDER BY '+ cast(@strOrderBy as nvarchar(255)) +' )) and '+@strwhere+' ORDER BY '+cast(@strOrderBy as varchar(255)) + ''
else
SET @strSQL = 'SELECT TOP '+ cast(@pageSize as nvarchar(10)) +' '+ @strGetField +' FROM '+ @strTableName +' WHERE ('+ cast(@SID as nvarchar(30)) +' NOT IN (SELECT TOP '+ cast(@num as nvarchar(20)) +' '+ cast(@SID as nvarchar(30)) +' FROM '+ @strTableName +' ORDER BY '+ cast(@strOrderBy as nvarchar(255)) +' )) ORDER BY '+ cast(@strOrderBy as varchar(255)) + ''
--SET @strSQL = 'SELECT TOP 页大小 * FROM Users WHERE (ID NOT IN (SELECT TOP (页大小*(当前页-1)) ID FROM Users ORDER BY ID DESC )) ORDER BY ID DESC'
END
ELSE
BEGIN
IF @strWhere != ''
SET @strSQL = 'select '+ @strGetField +' from ' + @strTableName + ' where '+ @strWhere +' order by '+ @strOrderBy
ELSE
SET @strSQL = 'select '+ @strGetField +' from ' + @strTableName + ' order by '+ @strOrderBy
END
--print(@strSQL)
EXEC(@strSQL)
GO

第二个存储过程

 

CREATE PROCEDURE [dbo].[USP_GetTotal]
(
@strTableName nvarchar(30) = '',
@strWhere nvarchar(2000) = '' -- 查询条件(注意: 条件中要带where)
)
AS
SET NOCOUNT OFF

DECLARE @strSQL nvarchar(2500)

IF @strWhere != ''
SET @strSQL = 'select count(*) as Total from ['+ @strTableName +'] where '+ @strWhere
ELSE
SET @strSQL = 'select count(*) as Total from ['+ @strTableName +']'

EXEC(@strSQL)
GO


下面是调用这个存储过程的两个函数可以放到一个包含文件里

,注意下面的函数里面使用的一些变量是全局的变量,不是参数传入的,所以需要在调用函数钱,给全局变量赋值

 

<%
'取记录总数存储过程
public function getDataRowCount(strTableName, strWhere)
dim maxCount
dim myobj
dim rsCount
maxCount = 0
Set myobj = Server.CreateObject("ADODB.Command")
with myobj
.ActiveConnection = conn
.CommandText = "USP_GetTotal"
.CommandType = 4
.Prepared = true
.Parameters.append .CreateParameter("@strTableName", 200, 1, 30,

    黑客防线网安服务器维护方案本篇连接:http://www.rongsen.com.cn/show-8841-1.html
网站维护教程更新时间:2012-03-20 05:01:48  【打印此页】  【关闭
我要申请本站N点 | 黑客防线官网 |  
专业服务器维护及网站维护手工安全搭建环境,网站安全加固服务。黑客防线网安服务器维护基地招商进行中!QQ:29769479

footer  footer  footer  footer