笔记148 的search存储过程 like语句

笔记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

 

posted @ 2013-08-03 23:18  桦仔  阅读(234)  评论(0)    收藏  举报