SQL Server 使用存储过程分页

Younly

<div id="cnblogs_post_body">-- 查询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