SQL query issue

Question

0.00/5 (No votes)

See more:

Hi all
I am new in sql server. I want to write a query that retrives data about student. It is like a piece of name that I enter.
I tried it but it does not work.

SQL

create proc select_name
(
@Name nvarchar(50)
)
as
begin
select *
from student
where
Name like '% @Name %'
end

Please, can anyone help me?

Thanks all

Posted 26-Dec-10 23:09pm

moon2011

Updated 26-Dec-10 23:20pm

Sandeep Mewara

v2

Add a Solution

Comments

Goutam Patra 27-Dec-10 5:24am

Check the updated answer

2 solutions

Solution 1

Send the % from your application that is mona% . That means youe @Name parameter will contain Mona% to find name started with mona. Then use where Name like @Name directly in your SP.

[EDIT]
Your Procedure

create proc select_name
(
@Name nvarchar(50)
)
as
begin
select *
from student
where
Name like @Name
end

Procedure Call

Exec select_name 'Mona%'

[END EDIT]

Posted 26-Dec-10 23:16pm

Goutam Patra

Updated 26-Dec-10 23:24pm

v2

Comments

moon2011 27-Dec-10 5:20am

can you make your answer simpler, please.

Sandeep Mewara 27-Dec-10 5:52am

My Vote of 4 for other approach suggested and pointing me the issue.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2010-12-26T23:24:00

Solution 2

Error lies here:
Name like '% @Name %'

You have given space after and before % in the where clause. Thus, it tries to find something like ' ant ' when you actually want words that has just 'ant'

Just remove the spaces and re-try.
It should be:

SQL

WHERE Name like '%'+@Name+'%'

Second mistake was parameter included inside the quotations which needs to be kept outside.

Posted 26-Dec-10 23:24pm

Sandeep Mewara

Updated 26-Dec-10 23:49pm

v3

Comments

Sandeep Mewara 27-Dec-10 5:31am

Was I wrong? Downvote for? :)

Sandeep Mewara 27-Dec-10 5:33am

Dont worry. User enters normal 'mon' and then in your SP, just put '%' before and pafter the word without spaces. All will be good. No need to pass on '%' appended from UI if that is not required as per your logic. Adding a '%' in SP is also fine.

Goutam Patra 27-Dec-10 5:38am

I didnt downvoted you.

@nuraGGupta@ 27-Dec-10 5:40am

Correct Answer lies here. I don't know who down votes correct answers here. This is really pathetic. Given 5 to counter.

moon2011 27-Dec-10 5:47am

Now, we return to the previous problem, when i put % in the stored, it didn't work in UI

Sandeep Mewara 27-Dec-10 5:50am

Just check the changed thing. It should work.

@nuraGGupta@ 27-Dec-10 5:50am

How could it be possible? If you are getting result running your query in query analyzer, then you will get results in UI also. Please note that you need not put any "%" now, when sending string from UI to DB. Just send the search string only.

moon2011 27-Dec-10 5:51am

But WHERE Name like '%@Name%' will search names stared with [%@Name%], not the value inside @Name var
so, it didn't work

Sandeep Mewara 27-Dec-10 5:53am

You have to change '%'+@Name+'%'

Did you made this change and tried? No spaces + parameter outside the quotation.

moon2011 27-Dec-10 6:01am

thaaaaaaaaaaanks alot, it works well now