Using LIKE inside Stored Procedure.

[EDN Admin]

I have a People table that has CompleteName, Address, and Phone. Im trying to look for the Name, and return the Phone number. I would like to use LIKE in the SELECT statement for the name.
The code in VB is this: <font color="#0000ff
<font size=2>Using</font></font><font size=2> Command <font color="#0000ff As</font> <font color="#0000ff New</font> SqlCommand(<font color="#800000 "SearchPeople"</font>, MyConnection)</font>
<font size=2>Command.CommandType = CommandType.StoredProcedure</font>
<font size=2>Command.Parameters.Add(<font color="#800000 "@Competename"</font>, SqlDbType.NVarChar).Value = Textbox1.Text</font>
<font size=2><font color="#0000ff Using</font> DataReader <font color="#0000ff As</font> SqlDataReader = Command.ExecuteReader()</font>
<font size=2><font color="#0000ff While</font> DataReader.Read</font>
<font size=2>write stuff to a file, DataReader(<font color="#800000 "Phone"</font>).ToString</font>
<font size=2><font color="#0000ff End</font> <font color="#0000ff While </font></font>
<font size=2><font color="#0000ff End</font> <font color="#0000ff Using </font></font>
<font size=2><font color="#0000ff End</font> <font color="#0000ff Using</font></font>
<font size=1><font color="#0000ff" size=3>The stored procedure code is this:</font></font>
<font size="+0 <font size=2><font color="#333399 <font style="background-color:#ffffff CREATE PROCEDURE [dbo].[SearchPeople]
@CompleteName nvarchar
AS
SELECT * FROM People
WHERE CompleteName LIKE @CompleteName
GO</font>
</font></font></font>
<font size="+0 <font color="#0000ff" size=2>I have another table that looks for ID number, it works great with similar code, except this particular one doesnt return anything. </font></font>
<font size="+0 <font color="#0000ff" size=2>Ive tried adding "%" & Textbox1.Text & "%"  also in <font color="#333399 WHERE CompleteName LIKE % + @CompleteName + "%</font>
</font></font><font size="+0 <font color="#0000ff" size=2></font></font>
<font size="+0 <font color="#0000ff" size=2>So how can I search using LIKE with this? If I dont use stored procedure and using sql commandtext, it works fine. </font></font>
<font size="+0 <font color="#0000ff" size=2>  </font></font>

View the full article