很強大的sqlserver分頁預存程序

代碼

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE ContractRecord_GetAll_Page 
-- Add the parameters for the stored procedure here
@filterExpression NVARCHAR(2000),
@sortExpression NVARCHAR(100),
@rowIndex INT = 0,
@pageSize INT = 24,        
@TotalRecords INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
CREATE TABLE #GoodsKind (RowNumber INT, ContractID uniqueidentifier)
IF ((@sortExpression IS NULL) OR (LEN(@sortExpression) = 0))
BEGIN
SET @sortExpression = 'ContractCode asc'
END    

DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = N'
        INSERT INTO #GoodsKind
        SELECT    ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowNumber, ContractID
        FROM vw_ContractRecord'
IF ((@filterExpression IS NOT NULL) AND (LEN(@filterExpression) > 0))
BEGIN
SET @SQLString = @SQLString + ' WHERE ' + @filterExpression
END        

EXECUTE sp_executesql @SQLString

SELECT    @TotalRecords = COUNT(ContractID)
FROM    #GoodsKind
-- Insert statements for procedure here
    SELECT p.ContractID,EntCode,ContractCode,GoodsLimitDepartment,Supplier,Merchandiser,ContractTime,
Remark,RealAmount,TaxAmount,RealAndTaxAmount,OperatorName,InputDate
FROM    #GoodsKind as p INNER JOIN vw_ContractRecord as C on p.ContractID = C.ContractID 
WHERE p.RowNumber BETWEEN @rowIndex + 1 AND @rowIndex + @PageSize ORDER BY p.RowNumber
END
GO