searching and indexing - 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

General SQL Server Forums

New to SQL Server Programming

searching and indexing

Author

Topic

cyankhan
Starting Member

7 Posts

Posted - 2008-04-13 : 17:28:26

i have to make the following but i have no clue any help will be appreciated

i have to search through three tables based on user preferences.

the tablkes are author name, book name and topics.( i have created ttables and their relations)

now i want to the user to select the option from the drop down menu. The problem is how do i ascertain(dynamically) which table to search based on the action selected by the user. Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-13 : 18:20:12

Create a stored procedure. Pass the search criteria to the SP as parameters and in the SP decide which tables to use in the slect staements depending on the parameters.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 00:10:59

This will provide you with a stub to work on:-

CREATE PROC MySearch
@SearchValue varchar(100)
@Criteria varchar(100)
AS
IF @Criteria='Author' 
SELECT fields FROM AuthorName WHERE Author=@SearchValue

IF @Criteria='Book'
SELECT fields FROM BookName WHERE Book=@SearchValue

IF @Criteria='Topics'
SELECT fields FROM Topics WHERE Topic=@SearchValue
GO

cyankhan
Starting Member

7 Posts

Posted - 2008-04-14 : 04:26:00

Thanks to both of you. However i am finding it diffuclt to pass the parameters to SP any examples? THAnks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 04:32:26

example: Exec MySearch @SearchValue='Charles Dickens',@Criteria='Author'

If you are still unclear,post what difficult you're facing with code used

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 05:47:24

It depends on your client app. What is it written in?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cyankhan
Starting Member

7 Posts

Posted - 2008-04-14 : 05:49:51

thank you very much Visakh. The problem is I really don't know how to implement ur suggestions.Now suppose on my search page i enter the search string as 'ankit' and select author in the dropdown menu.
now when i click on the search button
then exec my search @searchvalue=searchtextbox.text
but how to pass the parameters of the drop down menu
It would be awesome if you could point to any tutorial for passing parameters and calling SP.
Thanks again you have been very patient and kind

cyankhan
Starting Member

7 Posts

Posted - 2008-04-14 : 05:52:44

hi nr its written in C# (atleast thats what i hope to achieve)and i am constructing a search engine for the aforementioned databases for a asp.net website. Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 06:27:07

Then you will probably be using ado.net.
Create the command and add the parameters to it.
You should also create a database access layer to handle it all for you.

There's an example for vb.net here - c# is similar
http://www.nigelrivett.net/DOTNET/DotNetDBAccess.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cyankhan
Starting Member

7 Posts

Posted - 2008-04-14 : 11:11:48

thanks nr its a bit complicated for me but i will try if i can manage

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 12:14:48

might help you on this:-

http://www.softwarecodehelp.com/article/storeprocedure.aspx

cyankhan
Starting Member

7 Posts

Posted - 2008-04-14 : 15:40:59