Stored Procedure

[Blue Sky]

CREATE PROCEDURE sp_SearchName
@v2 char

AS
select name, sex from inf_tbl where name like @v2%

If the user input 'M' , the procedure can show all names start from 'Mxxxx',
but I can't show this .
What's wrong with this procedure ?
I think there is something wrong with operator 'like'.

[wbeetge]

Hi.

No there is nothing wrong with 'like'.

Try to concatenate the variable @v2 before inserting it into the select statement.
- Select @v2 = @v2 + '%'
- Select Name, Sex from Inf_Tbl where name like @v2.
(alternatative)
You may want to concatenate the '%' to the variable in your VB code before calling the stored proc. Then you would not need to do it in the stored proc

Let me know if you still have problems

[Blue Sky]

It can't work !!!

e.g. suppose chosen = "M"
chosen = chosen + '%' ----> here is the problem

sql = "Select * from inf_tbl where name like " & _
"'" & chosen & "'" & " order name"

[wbeetge]

A typical 'like' Statement :

Select * from Table where Name like 'A%'
and not
Select * from Table where Name like 'A'%

This is how your Supposed values must look before submitting it into the SQL string :
- suppose chosen = "M%"

Remember that the '%' sign is part of the value and not of the statement

Also don't confuse yourself with complicated concatenations :
Your line of code :
- sql = "Select * from inf_tbl where name like " & _
"'" & chosen & "'" & " order name"
My line of code:
- sql = "Select * from inf_tbl where name like '" & chosen & "' order by Name"

[Blue Sky]

It can work now !!!
wbeetage, thank you much for helping me to solve this problem !