SQL Server 使用存储过程分页

SQL Server 使用存储过程分页

 

-- 查询Company信息并分页
CREATE PROCEDURE [dbo].[Company_List]
 @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