>1. Don't use TEXT type, use Varchar(max). TEXT is going to be deprecated in future versions of SQL Server.
Ok, I changed it. I didn't know it was deprecated. Thanks

>>2. If you want to allow users to enter only the beginning part of the Product or Part Info I don't see a problem using two parameters for >>SP with LIKE @Product + '%'
Meaning something like this?

where product_no like '%C123%'

>>If you want to be able to search inside the product , then as suggested before you better set up full text search.
I googled Setting up SQL2005 Full Text Search and found this site:
http://kb.instantasp.co.uk/Documentation/Setting-up-full-text-search-within-InstantKB.NET

I also tried

CREATE FULLTEXT CATALOG my_catalog;

and got 'Full-Text Search is not installed, or a full-text component cannot be loaded.'

Also, in my DB I don't see 'Storage' node. Am I missing something?



>>You can also code search by Parts as a separate table-valued function. It may work quicker than JOINS.
Explain this please


I tried this and got back no data

DECLARE @ProductId				INT
DECLARE @Product_Series			VARCHAR(100)
DECLARE @Product_Number			VARCHAR(20)
DECLARE @Product_Name			VARCHAR(100)
DECLARE @Product_Description	VARCHAR(MAX)
DECLARE @PartId					INT
DECLARE @Part_Number			VARCHAR(100)
DECLARE @New_Part_Number		VARCHAR(100)
DECLARE @Keyword				VARCHAR(30)
DECLARE @Category				VARCHAR(30)
DECLARE @Part_Description		VARCHAR(MAX)

SET @Part_Number = '153099-001'

SELECT pr.*, pa.*
	FROM HPProductsData pr
	JOIN HPProductsPartsData pp ON pp.ProductId = pr.ProductId
	JOIN HPPartsData pa ON pa.PartId = pp.PartId
	WHERE	pr.ProductId			= ISNULL(@ProductId, pr.ProductId) AND
			pr.Product_Series		= ISNULL(@Product_Series, pr.Product_Series) AND
			pr.Product_Number		= ISNULL(@Product_Number, pr.Product_Number) AND
			pr.Product_Name			= ISNULL(@Product_Name, pr.Product_Name) AND
			pr.Product_Description	= ISNULL(@Product_Description, pr.Product_Description) AND
			pa.PartId				= ISNULL(@PartId, pa.PartId) AND
			pa.Part_Number			= ISNULL(@Part_Number, pa.Part_Number) AND
			pa.New_Part_Number		= ISNULL(@New_Part_Number, pa.New_Part_Number) AND
			pa.Keyword				= ISNULL(@Keyword, pa.Keyword) AND
			pa.Category				= ISNULL(@Category, pa.Category) AND
			pa.Part_Description		= ISNULL(@Part_Description, Part_Description)