学步园

搜索函数

 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

后台文件传入参数

protected void Button1_Click(object sender, EventArgs e)<br /> {<br /> string MyConn = WebConfigurationManager.ConnectionStrings["Conn"].ConnectionString;<br /> SqlConnection Myconnection = new SqlConnection(MyConn);</p> <p> SqlCommand cmd = new SqlCommand("SearchDog", Myconnection);<br /> cmd.CommandType = CommandType.StoredProcedure;<br /> cmd.Parameters.Add("@Word1", SqlDbType.NVarChar, 50);<br /> cmd.Parameters.Add("@Word2", SqlDbType.NVarChar, 50);<br /> cmd.Parameters.Add("@Word3", SqlDbType.NVarChar, 50);<br /> cmd.Parameters["@Word1"].Value = TextBox1.Text;<br /> cmd.Parameters["@Word2"].Value = TextBox2.Text;<br /> cmd.Parameters["@Word3"].Value = TextBox3.Text;<br /> cmd.Connection.Open();<br /> SqlDataAdapter adapter = new SqlDataAdapter(cmd);<br /> DataSet ds = new DataSet();<br /> Adapter.Fill(ds);<br /> GridView1.DataSource = ds;<br /> GridView1.DataBind();<br /> cmd.Connection.Close();<br /> }