Saerch Module - 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)

Saerch Module

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-11-22 : 12:56:26

Hi

I have a database that has different tables containg various type of customer information .

I have a front end to insert data in Asp.net 2.0.

I want to search the whole database based on the customer number,customer last name first name and by table name because each table is for a specific form in the front end

I mean customer number field can be in table so when the user searches for that customer and if he is present in two or more tables i want all the table names to show up . Is that possible in SQl.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 13:05:12

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-11-26 : 15:59:35

Thanks for you response visakh16

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-11-26 : 17:07:09

HI Visakh16

the code works but it just shows the coloum in which the search term is contained. What should i do to show the whole row in which the coloumn containing the search term is a part of.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 19:54:33

Well you have got tablename and columnname where searchword exists. You can query those tables and find what you are looking for.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-11-30 : 18:29:06

hi sodeep,

can you provide example query looking at the example provided by visakh 16.

thank you

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-30 : 19:37:29

Tell me how do you get whole row when you know tablename,columnname and search keyword?

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-11-30 : 19:50:10

sorry sodeep i did not understand what u said.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 23:51:53

change #Results in code to contain all your columns in table. also modify insert statement to include all columns in the table.some thing like:-


INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630),other columns here 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)

also trweak last select to select * from #Results

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-12-01 : 18:02:51

Thanks for you reply visakh 16.

But i am not able to do it correctly.

During the search results i want some standadr fields to show .
these fields are included in all the tables of the database.

I mean when i search by customer number it should display results like this

tablename,customer_nbr,customer_firstname,customer_lastname

when i do the search by customerlastname,firtsname
the same fields should be shown again

tablename,customer_nbr,customer_firstname,customer_lastname

hope i am clear.Please help.

thank you once again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 00:06:02

Then i think what you need is a CASE expression to show column values based on conditions

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-12-02 : 10:30:47

Visakh 16 can u provide me with a query on how to do that. I am new to transact -sql.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 11:09:25

quote:
Originally posted by akpaga

Visakh 16 can u provide me with a query on how to do that. I am new to transact -sql.

can you specify your exact requirement? what all columns need to be displayed based on what condition?

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-12-02 : 11:36:26

Hi visakh16

My requirement is as follows :

the user would search by customer number or customer lastname,firstname

when the do so the all the tables in the database should be searched

and the results should be displayed as

tablename,customer number,customerlastname,customerfirstname

if the search by customernumber and the any other table containing the customer number does not have customer lastname,firstname then it show blank values

ex: search term 1111(customer number)

result should look like this

customer(tablename),1111,ivar(lastname),arun(firstname)
articles(tablename),1111, -----(blank),---(blank)

same when the search with lastname,firstname.

hope i am clear. thank you very much

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-12-03 : 10:33:50

Hi visakh 16

can you help modify the search that you prvided teh link . Can it take two parameters like lastname,firstname

and then return the tablename and both the fields lastname.firstname of the table.

i mean when user enters arun,kumar
then it should return only the table which has the rows with lastname as arun and firstname as kumar.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources