Tuesday, February 28, 2006
Full Text Index Required for Some Functions
Since my install scripts are automated I wanted a way to conditionally install one of two versions of the same function. The solution was to use a special metadata function along with the EXEC command to create the function.
1 IF (SELECT fulltextcatalogproperty('MyFullTextCatalog', 'ItemCount')) IS NULL
2 BEGIN
3
4 EXEC('
5 CREATE FUNCTION [dbo].[udf_MySearchFunction]
6 (
7 @criteria AS VARCHAR(200)
8 )
9 RETURNS @retTable
10 TABLE
11 (
12 [seqid] [int] IDENTITY (1, 1) NOT NULL
13 ,[resultid] [int] NOT NULL
14 )
15 AS
16 BEGIN
17
18 INSERT INTO @retTable
19 SELECT [resultid]
20 FROM [MyTable]
21 WHERE
22 [Description] LIKE '%' + @criteria + '%'
23 OR [Keywords] LIKE '%' + @criteria + '%'
24
25 END
26
27 RETURN')
28
29 END
30 ELSE
31 BEGIN
32
33 EXEC('
34 CREATE FUNCTION [dbo].[udf_MySearchFunction]
35 (
36 @criteria AS VARCHAR(200)
37 )
38 RETURNS @retTable
39 TABLE
40 (
41 [seqid] [int] IDENTITY (1, 1) NOT NULL ,
42 [resultid] [int] NOT NULL
43 )
44 AS
45 BEGIN
46
47 INSERT INTO @retTable
48 SELECT [resultid]
49 FROM [MyTable]
50 WHERE FREETEXT([MyTable].*, @criteria)
51
52 END
53
54 RETURN')
55
56 END
57 GO
