c#.net+SQL2005 实现简单搜索

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,Rank FROM @Dog<br />WHERE Rank > 0<br />ORDER BY Rank DESC