搜索函数 USE [DATABASE] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE function [dbo].[search] (@Word nvarchar(max), @Phrase nvarchar(max)) returns smallint as begin if @Word is null or @Phrase is null return 0 DECLARE @BiggerWord VARCHAR(max) SELECT @BiggerWord = @Word + 'x' DECLARE @BiggerPhrase VARCHAR(max) SELECT @BiggerPhrase = REPLACE (@Phrase, @Word, @BiggerWord) RETURN LEN(@BiggerPhrase) - LEN(@Phrase) END 利用以上函数创建存储过程,提供3个参数查询条件 USE [DATABASE] 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) { string MyConn = WebConfigurationManager.ConnectionStrings["Conn"].ConnectionString; 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(); }