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.
CREATE FULLTEXT CATALOG my_catalog;
and got 'Full-Text Search is not installed, or a full-text component cannot be loaded.'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)