stored procedure- A simple query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure- A simple query

Author

Topic

alan_025
Starting Member

6 Posts

Posted - 2010-07-01 : 04:03:02

hello

I am new to sql and stored procedure ... i have created a table on student details and a stored procedure on that table...the searching is done with the name of the student ..

i want to know is there any way i can retrieve the student details even if we typed any letters matching those letters

Eg - suppose there is a student George Issac .. If someone typed George Samuel then also the particular student details want to display .. ie any searching that matches the stored student name.. i hope u understood my question

thank you

unplugged

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-01 : 04:51:48

Look for "sql server fulltext searching".

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 04:57:08

What do you want to return if there is student named samuel?

Madhivanan

Failing to plan is Planning to fail

alan_025
Starting Member

6 Posts

Posted - 2010-07-01 : 05:16:06

thank you madhivanan

If there is a student name samuel in the table ...also we have update of that student like contact no, percentage result of the student in other fields

Here in stored procedure a searching is done on that table with name ie student name , when we search the samuel his details will display.. also my stored procedure is working fine ..

But now the student details is displaying only we use the correct student name like samuel not sam or samuvel like that ..i want to know is there any option that will help to display the details even we entered any words that matches the studentname ..like sam we typed it displays samuel and other studentname matching sam

thank you

unplugged

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-01 : 05:42:20

Hello,

You can use LIKE operator. For example:

WHERE student_name LIKE @student_name+'%'

Best regards,

Devart Team

alan_025
Starting Member

6 Posts

Posted - 2010-07-01 : 05:53:30

thank you devart

this is my syntax

create procedure getstudentresult
@StudentName nvarchar(50)
AS
BEGIN
SELECT *from Students
where StudentName=@StudentName
END

..
Could u pls guide me how can i use like operator

unplugged

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-01 : 06:04:18

Hello,

create procedure getstudentresult
(@StudentName nvarchar(50))
AS
BEGIN
SELECT * from Students
WHERE StudentName LIKE @StudentName+'%'
END

Best regards,

alan_025
Starting Member

6 Posts

Posted - 2010-07-01 : 06:36:51

thank you devart ..this is working...

Also u know i have second name ..EG gerorge issac .. if i typed geo or georg the detail is diplaying ..if issac nothing dispalys ..

u have any more alternatives

unplugged

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-01 : 07:10:33

create procedure getstudentresult
(@StudentName nvarchar(50))
AS
BEGIN
set nocount on
declare @name1 nvarchar(50)
declare @name2 nvarchar(50)
declare @charindex int
set @studentname=rtrim(ltrim(@studentname))
set @charindex=charindex(' ',@studentname)

if @charindex>0
begin
set @name1=substring(@studentname,1,charindex(' ',@studentname)-1)+'%'
set @name2='%'+substring(@studentname,charindex(' ',@studentname)+1,datalength(@studentname))+'%'
end
else
begin
set @name1=@studentname+'%'
set @name2=@studentname+'%'
end

select
*
from students
where
studentname like @studentname+'%' or
studentname like @name1 or
studentname like @name2

Devart Team

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-01 : 07:24:40

quote:
Originally posted by Devart

Hello,

create procedure getstudentresult
(@StudentName nvarchar(50))
AS
BEGIN
SELECT * from Students
WHERE StudentName LIKE '%'+@StudentName+'%'
END

Best regards,

No, you're never too old to Yak'n'Roll if you're too young to die.

alan_025
Starting Member

6 Posts

Posted - 2010-07-01 : 07:35:04

thank you devart .. let me try with this syntax ...

unplugged

alan_025
Starting Member

6 Posts

Posted - 2010-07-01 : 07:39:32

Thank you webfred .. that one too working for me ...

unplugged

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources