asked on

How do I construct a stored procedure that will search table data based on keywords?

Greetings Experts,

I could use some help constructing a stored procedure that takes input from a textbox (Product Search), matches a Keywords field in a Products table, and returns the Product IDs.

I'm also looking for some logic that returns the strongest match if possible.

I've written a few stored procedures, but this is my first encounter with anything this dynamic.

I'm grateful for any assistance you can provide.

dublingills

I'm assuming your keywords column is a varchar column containing a list of possible keywords separated by a delimiter of some kind. In this case your stored procedure needs to use LIKE, ie

create procedure findallrecordsbykeyword
@VAL varchar(20)
as
select * from tableName where keywordColumnName like '%' + @VAL + '%';

This will return all matches where the value passed as the parameter (i.e. the value entered into your textbox) exists. Obviously the more characters you enter the fewer records will match.

HTH

bham3dman

ASKER

Thanks for the response. Yes, the Keywords column is a varchar column.

How would I match multiple keywords within this stored procedure and sort by the strongest match?

For instance, if a user inputs "Autodesk 3D Studio Max 2009" into the search field, I'd like to find matches for each keyword, and then order by the product with the most keyword matches.

ASKER CERTIFIED SOLUTION

dublingills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dublingills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bham3dman

ASKER

Thanks! Giving it a go...

bham3dman

ASKER

Worked like a charm. Thanks for the help!

nwncys

try this


/****** Object:  StoredProcedure [dbo].[SearchAllTables]    Script Date: 03/09/2009 21:29:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------**********************************--------------      
--EXEC SearchAllTables 'XXX'
------------**********************************--------------      
 
CREATE PROC [dbo].[SearchAllTables]
(
	@SearchStr nvarchar(100)
)
AS
BEGIN
 
 
	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
	SET NOCOUNT ON
 
	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)
 
		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END
 
	SELECT ColumnName, ColumnValue FROM #Results
END

Open in new window