USE [DATABASE]<br />
GO<br />
SET ANSI_NULLS ON<br />
GO<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
CREATE function [dbo].[search]<br />
(@Word nvarchar(max),<br />
@Phrase nvarchar(max))<br />
returns smallint<br />
as<br />
begin<br />
if @Word is null or @Phrase is null return 0<br />
DECLARE @BiggerWord VARCHAR(max)<br />
SELECT @BiggerWord = @Word + 'x'<br />
DECLARE @BiggerPhrase VARCHAR(max)<br />
SELECT @BiggerPhrase = REPLACE (@Phrase, @Word, @BiggerWord)<br />
RETURN LEN(@BiggerPhrase) - LEN(@Phrase)<br />
END

利用以上函数创建存储过程,提供3个参数查询条件

USE [DATABASE]<br />
GO</p>
<p>SET ANSI_NULLS ON<br />
GO<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
CREATR PROCEDURE [dbo].[SearchDog]<br />
( </p>
<p> @Word1 nVARCHAR(max)=null,<br />
@Word2 nVARCHAR(max)=null,<br />
@Word3 nVARCHAR(max)=null)<br />
AS<br />
DECLARE @Dog TABLE<br />
(<br />
DogHead nvarchar(max),<br />
DogBody nVARCHAR(max),<br />
Rank INT)</p>
<p> INSERT INTO @Dog<br />
SELECT DogHead, DogBody,<br />
3 * dbo.search(@Word1, DogHead) + dbo.search(@Word1, DogBody) +<br />
3 * dbo.search(@Word2, DogHead) + dbo.search(@Word2, DogBody) +<br />
3 * dbo.search(@Word3, DogHead) + dbo.search(@Word3, DogBody)<br />
AS Rank<br />
FROM Dog ORDER BY Rank DESC</p>
<p>SELECT DogHead, DogBody, Rank FROM @Dog<br />
WHERE Rank > 0<br />
ORDER BY Rank DESC