SQL Server 使用存储过程分页
<div id="cnblogs_post_body">-- 查询Company信息并分页CREATE PROCEDURE .
@InfoType INT, -- 信息类型
@PageCode VARCHAR(50), -- 频道ID
@Company VARCHAR(100), -- 企业名称
@PageSize INT, -- 每页记录数
@PageIndex INT -- 当前页码
AS
BEGIN
DECLARE @SQL VARCHAR(1000)
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = (@PageIndex-1)*@PageSize+1
SET @EndRow = @PageIndex*@PageSize
SET @SQL = 'WITH TEMPTABLE AS(SELECT CID,Company,Url,AltAttr,PubDate,SortIndex,PageCode,ROW_NUMBER()'
+' OVER(ORDER BY SortIndex DESC) AS Row FROM Company WHERE InfoType='+ CAST(@InfoType AS VARCHAR)
+' AND PageCode='''+ CAST(@PageCode AS VARCHAR) +''''
IF(@Company != '')
BEGIN
SET @SQL = @SQL +' AND Company LIKE ''%'+ CAST(@Company AS VARCHAR) +'%'''
END
SET @SQL = @SQL
+')SELECT *,(SELECT COUNT(1) FROM TEMPTABLE) AS PageCount FROM TEMPTABLE WHERE Row BETWEEN '
+ CAST(@StartRow AS VARCHAR) +' AND '+ CAST(@EndRow AS VARCHAR) + ' ORDER BY SortIndex DESC,PubDate DESC'
EXEC (@SQL)
END
页:
[1]