笔记148 的search存储过程 like语句
1 --的search存储过程 like语句
2 USE [GPOSDB]
3 GO
4 /****** 对象: StoredProcedure [dbo].[CT_Append_Search] 脚本日期: 02/04/2012 23:41:24 ******/
5 SET ANSI_NULLS ON
6 GO
7 SET QUOTED_IDENTIFIER ON
8 GO
9 -----------------------------------------------------------------
10 -- Name: CT_Append Store Procedure
11 -- Author:
12 -- DateTime: 2011-07-22
13 -- Description: Data TableCT_Append , Get List
14 -----------------------------------------------------------------
15 /*
16 @PageIndex
17 @TotalRecords
18 */
19 ALTER PROCEDURE [dbo].[CT_Append_Search]
20 (
21 @VC_A_SNNO varchar(50),
22 @VC_OC_UserName varchar(50),
23 @VC_OC_Company varchar(50),
24 @VC_A_CardNO varchar(50),
25 @CardType int,
26 @VC_A_AppendType varchar(50),
27 @VC_TicketType varchar(50),
28 @VC_TicketNO varchar(50),
29 @StartDate varchar(50),
30 @EndDate varchar(50),
31 @PageIndex int,
32 @TotalRecords int
33 )
34 AS
35 BEGIN
36 DECLARE @Page int
37 DECLARE @PageLowerBound int
38 DECLARE @PageUpperBound int
39 DECLARE @RowsToReturn int
40
41 SET @Page = (@PageIndex - 1)
42
43 -- First set the rowcount
44 SET @RowsToReturn = @TotalRecords * (@Page + 1)
45 SET ROWCOUNT @RowsToReturn
46
47 -- Set the page bounds
48 SET @PageLowerBound = @TotalRecords * @Page
49 SET @PageUpperBound = @PageLowerBound + @TotalRecords + 1
50
51 -- Create a temp table to store the select results
52 CREATE TABLE #PageIndex
53 (
54 IndexId int IDENTITY (1, 1) NOT NULL,
55 VC_A_SNNO varchar(50)
56 )
57 Declare @ConSQL varchar(2000)
58 Declare @StrSQL varchar(2000)
59 set @StrSQL='INSERT INTO #PageIndex (VC_A_SNNO) SELECT [VC_A_SNNO] FROM [CT_Append] a LEFT OUTER JOIN
60 CT_OuterCard b ON a.VC_A_CardNO = b.VC_OC_CardNO LEFT OUTER JOIN
61 CT_InhouseCard c ON a.VC_A_CardNO = c.VC_IC_CardNO where a.RecordNO<>0'
62 set @ConSQL=''
63 if(@VC_A_SNNO<>'')
64 begin
65 set @ConSQL=@ConSQL + ' and a.VC_A_SNNO like ''%' + @VC_A_SNNO +'%'''
66 end
67 if(@VC_OC_UserName<>'')
68 begin
69 set @ConSQL=@ConSQL + ' and b.VC_OC_UserName like ''%' + @VC_OC_UserName+'%'''
70 end
71 if(@VC_OC_Company<>'')
72 begin
73 set @ConSQL=@ConSQL + ' and b.VC_OC_Company like ''%' + @VC_OC_Company+'%'''
74 end
75 if(@VC_A_CardNO<>'')
76 begin
77 set @ConSQL=@ConSQL + ' and a.VC_A_CardNO like ''%' + @VC_A_CardNO+'%'''
78 end
79 if(@CardType>0)
80 begin
81 set @ConSQL=@ConSQL + ' and a.I_A_CardType=' + cast(@CardType as varchar(2))
82 end
83 if(@VC_A_AppendType<>'')
84 begin
85 set @ConSQL=@ConSQL + ' and a.VC_A_AppendType=''' + @VC_A_AppendType+''''
86 end
87 if(@VC_TicketType<>'')
88 begin
89 set @ConSQL=@ConSQL + ' and a.VC_TicketType=''' + @VC_TicketType+''''
90 end
91 if(@VC_TicketNO<>'')
92 begin
93 set @ConSQL=@ConSQL + ' and a.VC_TicketNO=''' + @VC_TicketNO+''''
94 end
95 if(@StartDate<>'')
96 begin
97 set @ConSQL=@ConSQL + ' and a.D_A_AppendDateTime>=''' + @StartDate+''''
98 end
99 if(@EndDate<>'')
100 begin
101 set @ConSQL=@ConSQL + ' and a.D_A_AppendDateTime<=''' + @EndDate+''''
102 end
103 Execute(@StrSQL + @ConSQL +' order by a.D_A_AppendDateTime DESC')
104
105
106 SELECT
107 c.VC_A_SNNO,
108 [VC_A_AppendType] ,
109 [VC_A_CardNO] ,
110 [I_A_CardType] ,
111 [I_A_PointToOil] ,
112 [VC_TicketType] ,
113 [VC_TicketNO] ,
114 [DE_A_BAmount] ,
115 [DE_A_AppendAmount] ,
116 [DE_A_AAmount] ,
117 [D_A_AppendDateTime] ,
118 [VC_A_Remark] ,
119 [VC_A_OperatorNO]
120 FROM [CT_Append] c , #PageIndex PageIndex
121 WHERE
122 c.VC_A_SNNO = PageIndex.VC_A_SNNO AND
123 PageIndex.IndexID > @PageLowerBound AND
124 PageIndex.IndexID < @PageUpperBound
125
126
127 Execute('SELECT COUNT(VC_A_SNNO) AS TotalRecords FROM [CT_Append] a LEFT OUTER JOIN
128 CT_OuterCard b ON a.VC_A_CardNO = b.VC_OC_CardNO LEFT OUTER JOIN
129 CT_InhouseCard c ON a.VC_A_CardNO = c.VC_IC_CardNO where a.RecordNO<>0' + @ConSQL)
130 END
