在SQL Server 2005中用存储过程实现搜索功能

 

 

 四、对结果进行分页

搜索的结果可能很多，对结果分页可以提高性能。我在 如何在数据层分页以提高性能已经说明了如何用存储过程进行分页了，这里就不在详细复述了。

过程简单来说就是创建一个临时表，表中包含行号，读取时按行号来读取数据

五、完整代码

     经过前面的分析，完整代码如下：

  1 Create  PROCEDURE  SearchCatalog 
  2 (      
  3   @PageNumber  TINYINT ,
  4   @ProductsPerPage  TINYINT ,
  5   @HowManyResults  SMALLINT  OUTPUT,
  6   @AllWords  BIT ,
  7   @Word1  VARCHAR ( 15 )  =  NULL ,
  8   @Word2  VARCHAR ( 15 )  =  NULL ,
  9   @Word3  VARCHAR ( 15 )  =  NULL ,
 10   @Word4  VARCHAR ( 15 )  =  NULL ,
 11   @Word5  VARCHAR ( 15 )  =  NULL )
 12 AS
 13 /**/ /* 创建临时表，保存搜索的结果（Sql Server2005适用，Sql Server2000见如何在数据层分页以提高性能） */
 14 DECLARE  @Products  TABLE
 15 (RowNumber  SMALLINT  IDENTITY  ( 1 , 1 )  NOT  NULL ,
 16  ID  INT ,
 17  Name  VARCHAR ( 50 ),
 18  Description  VARCHAR ( 1000 ),
 19 Rank  INT )
 20
 21 /**/ /* Any-words search */
 22 IF  @AllWords  =  0  
 23     Insert  INTO  @Products            
 24     Select  ID, Name, Description,
 25          3  *  dbo.WordCount( @Word1 , Name)  +  dbo.WordCount( @Word1 , Description)  +
 26
 27          3  *  dbo.WordCount( @Word2 , Name)  +  dbo.WordCount( @Word2 , Description)  +
 28
 29          3  *  dbo.WordCount( @Word3 , Name)  +  dbo.WordCount( @Word3 , Description)  +
 30
 31          3  *  dbo.WordCount( @Word4 , Name)  +  dbo.WordCount( @Word4 , Description)  +
 32
 33          3  *  dbo.WordCount( @Word5 , Name)  +  dbo.WordCount( @Word5 , Description) 
 34
 35            AS  Rank
 36
 37     FROM  Product
 38     ORDER  BY  Rank  DESC
 39
 40 /**/ /* all-words search */
 41
 42 IF  @AllWords  =  1
 43
 44     Insert  INTO  @Products            
 45
 46     Select  ID, Name, Description,
 47
 48           ( 3  *  dbo.WordCount( @Word1 , Name)  +  dbo.WordCount
 49
 50 ( @Word1 , Description))  *
 51
 52            CASE  
 53
 54             WHEN  @Word2  IS  NULL  THEN  1  
 55
 56             ELSE  3  *  dbo.WordCount( @Word2 , Name)  +  dbo.WordCount( @Word2 , 
 57
 58 Description)
 59
 60            END  *
 61
 62            CASE  
 63
 64             WHEN  @Word3  IS  NULL  THEN  1  
 65
 66             ELSE  3  *  dbo.WordCount( @Word3 , Name)  +  dbo.WordCount( @Word3 , 
 67
 68 Description)
 69
 70            END  *
 71
 72            CASE  
 73
 74             WHEN  @Word4  IS  NULL  THEN  1  
 75
 76             ELSE  3  *  dbo.WordCount( @Word4 , Name)  +  dbo.WordCount( @Word4 , 
 77
 78 Description)
 79
 80            END  *
 81
 82            CASE  
 83
 84             WHEN  @Word5  IS  NULL  THEN  1  
 85
 86             ELSE  3  *  dbo.WordCount( @Word5 , Name)  +  dbo.WordCount( @Word5 , 
 87
 88 Description)
 89
 90            END
 91
 92            AS  Rank
 93
 94     FROM  Product
 95
 96     ORDER  BY  Rank  DESC
 97
 98 /**/ /* 在外部变量保存搜索结果数 */
 99
100 Select  @HowManyResults  =  COUNT ( * ) 
101
102 FROM  @Products  
103
104 Where  Rank  >  0
105
106 /**/ /* 按页返回结果*/
107
108 Select  ProductID, Name, Description, Price, Image1FileName,
109
110  Image2FileName, Rank
111
112 FROM  @Products
113
114 Where  Rank  >  0
115
116    AND  RowNumber  BETWEEN  ( @PageNumber - 1 )  *  @ProductsPerPage  +  1  
117
118                      AND  @PageNumber  *  @ProductsPerPage
119 ORDER  BY  Rank  DESC

 

  至此一个简单的搜索算法就实现了。