搜索函数

 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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATR PROCEDURE [dbo].[SearchDog]
(

@Word1 nVARCHAR(max)=null,
@Word2 nVARCHAR(max)=null,
@Word3 nVARCHAR(max)=null)
AS
DECLARE @Dog TABLE
(
DogHead nvarchar(max),
DogBody nVARCHAR(max),
Rank INT)

INSERT INTO @Dog
SELECT DogHead, DogBody,
3 * dbo.search(@Word1, DogHead) + dbo.search(@Word1, DogBody) +
3 * dbo.search(@Word2, DogHead) + dbo.search(@Word2, DogBody) +
3 * dbo.search(@Word3, DogHead) + dbo.search(@Word3, DogBody)
AS Rank
FROM Dog ORDER BY Rank DESC

SELECT DogHead, DogBody, Rank FROM @Dog
WHERE Rank > 0
ORDER BY Rank DESC

后台文件传入参数

protected void Button1_Click(object sender, EventArgs e)<br/>{<br/>string MyConn = WebConfigurationManager.ConnectionStrings["Conn"].ConnectionString;<br/>SqlConnection Myconnection = new SqlConnection(MyConn);

SqlCommand cmd = new SqlCommand("SearchDog", Myconnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Word1", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@Word2", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@Word3", SqlDbType.NVarChar, 50);
cmd.Parameters["@Word1"].Value = TextBox1.Text;
cmd.Parameters["@Word2"].Value = TextBox2.Text;
cmd.Parameters["@Word3"].Value = TextBox3.Text;
cmd.Connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
Adapter.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
cmd.Connection.Close();
}