1 USE [IPP_PCL] 2 GO 3 /****** Object:  StoredProcedure [dbo].[Printed_GetEOrderPrintedStatus]    Script Date: 11/04/2016 17:29:40 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8  9 ALTER proc [dbo].[Printed_GetEOrderPrintedStatus]10 @intPageIndex int,--当前页索引11 @intPageSize int,--每页显示的记录数12 @strEOrderNo nvarchar(50),13 @strLoginId nvarchar(50),14 @strCustomerItemCode nvarchar(30),15 @strProductCode nvarchar(30),16 @strCostomerPO nvarchar(30),17 @dateOrderDateCreateFrom datetime,18 @dateOrderDateCreateTo datetime,19 @strMainProgram nvarchar(50),20 @strSubProgram nvarchar(50),21 22 @intReslut int OUTPUT,23 @intTotal int OUTPUT --总记录数24 as25 BEGIN26     DECLARE @strWhere NVARCHAR(1000) = ‘‘27     DECLARE @strOrderBy NVARCHAR(50) = ‘ORDER BY OrderCreatedDate desc‘28   29     --根据参数生成where条件30     IF ISNULL(@strEOrderNo, ‘‘) <> ‘‘ 31     SET @strWhere = @strWhere + ‘ AND EOrderNo like ‘‘%‘ + @strEOrderNo + ‘%‘‘‘ 32     33     IF ISNULL(@strLoginId, ‘‘) <> ‘‘ 34     SET @strWhere = @strWhere + ‘ AND LoginId like ‘‘%‘ + @strLoginId + ‘%‘‘‘ 35     36     IF ISNULL(@strCustomerItemCode, ‘‘) <> ‘‘ 37     SET @strWhere = @strWhere + ‘ AND CustomerItemCode like ‘‘%‘ + @strCustomerItemCode + ‘%‘‘‘ 38     39     IF ISNULL(@strProductCode, ‘‘) <> ‘‘ 40     SET @strWhere = @strWhere + ‘ AND ProductCode like ‘‘%‘ + @strProductCode + ‘%‘‘‘ 41     42     IF ISNULL(@strCostomerPO, ‘‘) <> ‘‘ 43     SET @strWhere = @strWhere + ‘ AND CustomerPo like ‘‘%‘ + @strCostomerPO + ‘%‘‘‘ 44     45     IF @dateOrderDateCreateFrom IS NOT NULL46     SET @strWhere = @strWhere + ‘ AND OrderCreatedDate >= ‘‘‘ + CONVERT(VARCHAR(100),@dateOrderDateCreateFrom,121) + ‘‘‘‘47    48     IF @dateOrderDateCreateTo IS NOT NULL49     SET @strWhere = @strWhere + ‘ AND OrderCreatedDate < ‘‘‘ + CONVERT(VARCHAR(100),DATEADD(day,1,@dateOrderDateCreateTo),121) + ‘‘‘‘50     51     IF ISNULL(@strMainProgram, ‘‘) <> ‘‘ 52     SET @strWhere = @strWhere + ‘ AND MainProgram = ‘‘‘ + @strMainProgram + ‘‘‘‘53     54     IF ISNULL(@strSubProgram, ‘‘) <> ‘‘ 55     SET @strWhere = @strWhere + ‘ AND SubProgram =  ‘‘‘ + @strSubProgram + ‘‘‘‘56 57     --获取记录总数58     DECLARE @strCountSql NVARCHAR(1000)59     SET @strCountSql = ‘Select @RecordCount = count(*) FROM Printed_EOrderPrintedStatus WHERE 1 = 1 ‘ + @strWhere60     EXEC sp_executesql @strCountSql, N‘@RecordCount int OUTPUT‘, @intTotal OUTPUT61    62     63     DECLARE @strSql NVARCHAR(1000)64     SET @strSql = ‘SELECT TOP ‘ + CONVERT(VARCHAR(10),@intPageIndex * @intPageSize) + ‘ ROW_NUMBER() OVER(‘ + @strOrderBy + ‘) AS rownum, * FROM Printed_EOrderPrintedStatus WHERE 1 = 1‘ + @strWhere + @strOrderBy65     SET @strSql = ‘SELECT * FROM (‘ + @strSql + ‘) as temp WHERE temp.rownum > ‘ + CONVERT(VARCHAR(10), (@intPageSize * (@intPageIndex - 1)))66     EXEC(@strSql)67     68     if(@intTotal>0)69     begin70         set @intReslut=171     end72     73 end

 1    DECLARE @intPageIndex INT = 36 2    DECLARE @intPageSize INT = 10 -- int 3    DECLARE @strEOrderNo NVARCHAR(50) = N‘‘ -- nvarchar(50) 4    DECLARE @strLoginId NVARCHAR(50) = N‘‘ -- nvarchar(50) 5    DECLARE @strCustomerItemCode nvarchar(30) = N‘‘ -- nvarchar(30) 6    DECLARE @strProductCode nvarchar(30) = N‘10‘ -- nvarchar(30) 7    DECLARE @strCostomerPO nvarchar(30) = N‘‘ -- nvarchar(30) 8    DECLARE @dateOrderDateCreateFrom DATETIME = ‘2016-1-18 03:59:00‘ -- datetime 9    DECLARE @dateOrderDateCreateTo DATETIME = ‘2016-10-18 03:59:00‘ -- datetime10    DECLARE @strMainProgram nvarchar(50) = N‘88‘ -- nvarchar(50)11    DECLARE @strSubProgram nvarchar(50) = N‘‘ -- nvarchar(50)12    DECLARE @intReslut INT = 0 -- int13    DECLARE @intTotal INT = 0 -- int14 15 EXEC dbo.Printed_GetEOrderPrintedStatus @intPageIndex, 16     @intPageSize, 17     @strEOrderNo, 18     @strLoginId, 19     @strCustomerItemCode, 20     @strProductCode, 21     @strCostomerPO,22     @dateOrderDateCreateFrom, -- datetime23     @dateOrderDateCreateTo, -- datetime24     @strMainProgram, -- nvarchar(50)25     @strSubProgram, -- nvarchar(50)26     @intReslut OUTPUT, -- int27     @intTotal OUTPUT -- int28 29 SELECT @intTotal30 SELECT @intReslut