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

1 CREATE FUNCTION dbo.WordCount
2
3 ( @Word VARCHAR( 15),
4
5 @Phrase VARCHAR( 1000))
6
7 RETURNS SMALLINT
8
9 AS
10
11 BEGIN
12
13 /**/ /* 如果@Word 或者@Phrase 为空返回 0 */
14
15 IF @Word IS NULL OR @Phrase IS NULL RETURN 0
16
17 /**/ /* @BiggerWord 比@Word长一个字符 */
18
19 DECLARE @BiggerWord VARCHAR( 21)
20
21 SELECT @BiggerWord = @Word + ' x '
22
23 /**/ /*在 @Phrase用@BiggerWord替换@Word */
24
25 DECLARE @BiggerPhrase VARCHAR( 2000)
26
27 SELECT @BiggerPhrase = REPLACE ( @Phrase, @Word, @BiggerWord)
28
29 /**/ /* 相减结果就是出现的次数了 */
30
31 RETURN LEN( @BiggerPhrase) - LEN( @Phrase)
32
33 END
34

1 SELECT Product.Name,
2 3 * WordCount( @Word1, Name) + WordCount( @Word1, Description) +
3
4 3 * WordCount( @Word2, Name) + WordCount( @Word2, Description) +
5
6
7
8 AS Rank
9 FROM Product
10
11

1 SELECT Product.Name,
2
3 ( 3 * WordCount( @Word1, Name) + WordCount( @Word1, Description)) *
4
5 CASE
6
7 WHEN @Word2 IS NULL THEN 1
8
9 ELSE 3 * WordCount( @Word2, Name) + WordCount( @Word2, Description)
10
11 END *
12
13
14
15 AS Rank
16
17 FROM Product
18

1 SELECT Product.Name,
2 CASE
3 WHEN @Word1 IS NULL THEN 0
4 ELSE ISNULL( NULLIF(dbo.WordCount( @Word1, Name + ' ' + Description), 0), - 1000)
5 END +
6 CASE
7 WHEN @Word2 IS NULL THEN 0
8 ELSE ISNULL( NULLIF(dbo.WordCount( @Word2, Name + ' ' + Description), 0), - 1000)
9 END +
10
11 AS Rank
12 FROM Product

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

本文转自高海东博客园博客，原文链接：http://www.cnblogs.com/ghd258/archive/2006/11/17/563218.html，如需转载请自行联系原作者