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 2000 Forums  SQL Server Development (2000)  Need help to build a Store Procedure

Author  Topic  pmak Starting Member 41 Posts Posted - 2006-03-30 : 14:23:04 I need help to create a "Keywords" search function for the Sql databases. I work in a Health Research company and we have over 20 health research databases from other research companies. I need to find out who's doing what with the end user type in a "Keyword(s)" and the store procedure will search those databases and return with a dataset for a report. I am using Sql 2005 Reporting Services. In the report design, there is a textbox which end-user can type in a single keyword or multiple keywords seperate by ", " or ",". Also there is a dropdown for the end user to select the database(s) to search (Sql 2005 offers multiple selections in the dropdown. Great!). All health research databases come in with different size and columns. I have Full-Text indexed on all the databases to improve the performance in the search. The search result will write to a "temp" table with the 10 pre-defined columns which can be mapped to all databases, for reporting. Also no duplicate record is allowed in the temp table if the same record is found by more than one keyword in a database. The search itself will be "CONTAINS Predicate" searched on the full-text index database(s). Thanks a million! Eg. Keywords: Cancer, Smoking Databases selected: Cancer Research and XYZ Research Cancer_Research Table has 15 Columns eg. CR_col1...CR_col2...CR_col3 to CR_col15 XYZ_Research table has 6 columns eg. XYZ_col1...XYZ_col2... to XYZ_col6 Temp table has 10 columns eg. T_col1...T_col2... to T_col10 Mapping of columns to Temp table eg Cancer_Research Table CR_col1 to T_col1 CR_col3 to T_col2 CR_col4 to T_col3 CR_col6 to T_col4 CR_col8 to T_col5 CR_col9 to T_col6 CR_col11 to T_col7 CR_col13 to T_col8 CR_col14 to T_col9 CR_col15 to T_col10 XYZ_Research Table CR_col1 to T_col1 CR_col2 to T_col4 CR_col3 to T_col7 CR_col4 to T_col8 CR_col5 to T_col9 CR_col6 to T_col10 Also an additional column in the Temp table to capture the source of the record ie can be statically assigned in the query like "Cancer Reserach". The Sql query will be dynamically generated based on the end user selction on the choice of the full-text index databases and it will loop through each of the keyword to perform the search. PS It will be ideal if I can have another column in the Temp table to capture the keyword or keywords sepseated by "' " found on that record. Many thanks.