How can i overcome this prob? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

How can i overcome this prob?

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-08-11 : 23:46:35

Hello friends....

I have a table in that I am maintaining all the student personal information (like firstname,lastname,middlename,age,date of birth,city,state,country,phoneno,mob no,studentid etc).

I have to search the student information based user input. Stored procedure doesn't knw which one its going to get as input.

CREATE PROC PRCSEARCH
(
@SEARCHVALUE VARCHAR(20)
)

AS

BEGIN
-------------------------
END

based on the search value provided it shud search the records.

How can i do this.

Thanks in advance

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 23:49:16

this should give you a start
http://www.mssqltips.com/tip.asp?tip=1522

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 23:50:34

[code]
select *
from students
where firstname like '%' + @searchvalue + '%'
or lastname like '%' + @searchvalue + '%'
or middlename like '%' + @searchvalue + '%'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-08-11 : 23:59:58

Thanks for the reply KH

i have some doubt. This is what I have returned exactly in my proj.But i have small doubt that is if we have less records this will gets fine and very fast.

when we have lacks of records i feel it will take lot of time to find.because it should search each and every column.

Is there any alternate to do this without using cursors.

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 00:17:06

Another method without use of cursor. the link below gives code for seraching on all tables in db. you can modify it to do search only on a table.
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-12 : 01:17:58

You can check the Statistics on


select *
from   students
where  firstname  like '%' + @searchvalue + '%'
or     lastname   like '%' + @searchvalue + '%'
or     middlename like '%' + @searchvalue + '%'


select *
from   students
where  firstname  like '%' + @searchvalue + '%'
Union
select *
from   students
where  LastName  like '%' + @searchvalue + '%'
Union
select *
from   students
where  MiddleName  like '%' + @searchvalue + '%'

etc..

To see how the queries compare. If you have a ton of OR conditions, the Union queries might be a better alternative. I've run into a lot of scenerious where I tried keeping all my conditions using 1 query, and the end result was using seperate queries far out performed.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-12 : 01:33:13

Also just for another comparison I would try this


select *
from   students
where firstname +lastname +middlename+age+[date of birth]+city+state+country+phoneno+[mob no]+studentiD like '%' + @searchvalue + '%'

of course make sure all the columns are varchar/nvarchar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 01:40:14

And not null also. if any one of them are nullable,handle nulls using coalesce or is null.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources