1、定义存储过程
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 as 25 BEGIN 26 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 NULL 46 SET @strWhere = @strWhere + ' AND OrderCreatedDate >= ''' + CONVERT(VARCHAR(100),@dateOrderDateCreateFrom,121) + '''' 47 48 IF @dateOrderDateCreateTo IS NOT NULL 49 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 ' + @strWhere 60 EXEC sp_executesql @strCountSql, N'@RecordCount int OUTPUT', @intTotal OUTPUT 61 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 + @strOrderBy 65 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 begin 70 set @intReslut=1 71 end 72 73 end
2、书写时应注意的点
(1) 定义@strWhere变量后，应及时初始化。
(2) 拼接SQL条件时，应注意用''包括字段值。
(3) 应把时间变量转换成特定格式的字符串
(4) EXEC()
(5) EXEC sp_executesql
3、调用存储过程
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' -- datetime 10 DECLARE @strMainProgram nvarchar(50) = N'88' -- nvarchar(50) 11 DECLARE @strSubProgram nvarchar(50) = N'' -- nvarchar(50) 12 DECLARE @intReslut INT = 0 -- int 13 DECLARE @intTotal INT = 0 -- int 14 15 EXEC dbo.Printed_GetEOrderPrintedStatus @intPageIndex, 16 @intPageSize, 17 @strEOrderNo, 18 @strLoginId, 19 @strCustomerItemCode, 20 @strProductCode, 21 @strCostomerPO, 22 @dateOrderDateCreateFrom, -- datetime 23 @dateOrderDateCreateTo, -- datetime 24 @strMainProgram, -- nvarchar(50) 25 @strSubProgram, -- nvarchar(50) 26 @intReslut OUTPUT, -- int 27 @intTotal OUTPUT -- int 28 29 SELECT @intTotal 30 SELECT @intReslut
