searching employee names on an @parameter in SQL

Question

Possible Duplicate:
Parameterized Queries with LIKE and IN conditions

I try in my VB.net application to search persons on characters put in a text box by a parameter in sql DB. Something like-

select * from employee where name like '@name%'

but it doesn't work!

Can somebody help me?

"it doesn't work" makes not really a question. – H H Jul 30 '11 at 09:14 — H H, Jul 30 '11 at 09:14

score 3 · Answer 1 · answered Jul 29 '11 at 07:33

For a starts-with match:

SELECT * FROM employee WHERE name LIKE @name + '%'

For a contains match:

SELECT * FROM employee WHERE name LIKE '%' + @name + '%'

You want to combine the string '%' with the value of @name: currently you are using the string '@name%' which of course is not what you want to search for.

score 2 · Answer 2 · answered Jul 29 '11 at 07:34

2

If you want to handle this in SQL you can also do the following:

select * from employee where name like @name +'%'

Edit: Kieren Johnson beat me to this answer and has explained further

answered Jul 29 '11 at 07:34

WT_W

235
1
3
11

score 0 · Answer 3 · answered Jul 29 '11 at 07:30

you have to put the % at the end of your named parameter.

select * from employee where name like @name

addParameter("@name", "obama%") //this isn't actually a real function (because I don't know how you would call in in VB, but I think you see what i mean ;-))

also you should remove the ' around your named parameter

searching employee names on an @parameter in SQL

3 Answers3