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

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

SQL query issue

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

SQL query issue

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us