分页存储过程--同时返回数据总数

1、定义存储过程

USE [IPP_PCL]
GO
/****** Object:  StoredProcedure [dbo].[Printed_GetEOrderPrintedStatus]    Script Date: 11/04/2016 17:29:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[Printed_GetEOrderPrintedStatus]
@intPageIndex int,--当前页索引
@intPageSize int,--每页显示的记录数
@strEOrderNo nvarchar(50),
@strLoginId nvarchar(50),
@strCustomerItemCode nvarchar(30),
@strProductCode nvarchar(30),
@strCostomerPO nvarchar(30),
@dateOrderDateCreateFrom datetime,
@dateOrderDateCreateTo datetime,
@strMainProgram nvarchar(50),
@strSubProgram nvarchar(50),

@intReslut int OUTPUT,
@intTotal int OUTPUT --总记录数
as
BEGIN
    DECLARE @strWhere NVARCHAR(1000) = ''
    DECLARE @strOrderBy NVARCHAR(50) = 'ORDER BY OrderCreatedDate desc'
  
    --根据参数生成where条件
    IF ISNULL(@strEOrderNo, '') <> '' 
    SET @strWhere = @strWhere + ' AND EOrderNo like ''%' + @strEOrderNo + '%''' 
    
    IF ISNULL(@strLoginId, '') <> '' 
    SET @strWhere = @strWhere + ' AND LoginId like ''%' + @strLoginId + '%''' 
    
    IF ISNULL(@strCustomerItemCode, '') <> '' 
    SET @strWhere = @strWhere + ' AND CustomerItemCode like ''%' + @strCustomerItemCode + '%''' 
    
    IF ISNULL(@strProductCode, '') <> '' 
    SET @strWhere = @strWhere + ' AND ProductCode like ''%' + @strProductCode + '%''' 
    
    IF ISNULL(@strCostomerPO, '') <> '' 
    SET @strWhere = @strWhere + ' AND CustomerPo like ''%' + @strCostomerPO + '%''' 
    
    IF @dateOrderDateCreateFrom IS NOT NULL
    SET @strWhere = @strWhere + ' AND OrderCreatedDate >= ''' + CONVERT(VARCHAR(100),@dateOrderDateCreateFrom,121) + ''''
   
    IF @dateOrderDateCreateTo IS NOT NULL
    SET @strWhere = @strWhere + ' AND OrderCreatedDate < ''' + CONVERT(VARCHAR(100),DATEADD(day,1,@dateOrderDateCreateTo),121) + ''''
    
    IF ISNULL(@strMainProgram, '') <> '' 
    SET @strWhere = @strWhere + ' AND MainProgram = ''' + @strMainProgram + ''''
    
    IF ISNULL(@strSubProgram, '') <> '' 
    SET @strWhere = @strWhere + ' AND SubProgram =  ''' + @strSubProgram + ''''

    --获取记录总数
    DECLARE @strCountSql NVARCHAR(1000)
    SET @strCountSql = 'Select @RecordCount = count(*) FROM Printed_EOrderPrintedStatus WHERE 1 = 1 ' + @strWhere
    EXEC sp_executesql @strCountSql, N'@RecordCount int OUTPUT', @intTotal OUTPUT
   
    
    DECLARE @strSql NVARCHAR(1000)
    SET @strSql = 'SELECT TOP ' + CONVERT(VARCHAR(10),@intPageIndex * @intPageSize) + ' ROW_NUMBER() OVER(' + @strOrderBy + ') AS rownum, * FROM Printed_EOrderPrintedStatus WHERE 1 = 1' + @strWhere + @strOrderBy
    SET @strSql = 'SELECT * FROM (' + @strSql + ') as temp WHERE temp.rownum > ' + CONVERT(VARCHAR(10), (@intPageSize * (@intPageIndex - 1)))
    EXEC(@strSql)
    
    if(@intTotal>0)
    begin
        set @intReslut=1
    end
    
end

2、书写时应注意的点

(1) 定义@strWhere变量后，应及时初始化。

(2) 拼接SQL条件时，应注意用''包括字段值。

(3) 应把时间变量转换成特定格式的字符串

(4) EXEC()

(5) EXEC sp_executesql

3、调用存储过程

   DECLARE @intPageIndex INT = 36
   DECLARE @intPageSize INT = 10 -- int
   DECLARE @strEOrderNo NVARCHAR(50) = N'' -- nvarchar(50)
   DECLARE @strLoginId NVARCHAR(50) = N'' -- nvarchar(50)
   DECLARE @strCustomerItemCode nvarchar(30) = N'' -- nvarchar(30)
   DECLARE @strProductCode nvarchar(30) = N'10' -- nvarchar(30)
   DECLARE @strCostomerPO nvarchar(30) = N'' -- nvarchar(30)
   DECLARE @dateOrderDateCreateFrom DATETIME = '2016-1-18 03:59:00' -- datetime
   DECLARE @dateOrderDateCreateTo DATETIME = '2016-10-18 03:59:00' -- datetime
   DECLARE @strMainProgram nvarchar(50) = N'88' -- nvarchar(50)
   DECLARE @strSubProgram nvarchar(50) = N'' -- nvarchar(50)
   DECLARE @intReslut INT = 0 -- int
   DECLARE @intTotal INT = 0 -- int

EXEC dbo.Printed_GetEOrderPrintedStatus @intPageIndex, 
    @intPageSize, 
    @strEOrderNo, 
    @strLoginId, 
    @strCustomerItemCode, 
    @strProductCode, 
    @strCostomerPO,
    @dateOrderDateCreateFrom, -- datetime
    @dateOrderDateCreateTo, -- datetime
    @strMainProgram, -- nvarchar(50)
    @strSubProgram, -- nvarchar(50)
    @intReslut OUTPUT, -- int
    @intTotal OUTPUT -- int

SELECT @intTotal
SELECT @intReslut