DECLARE @SearchWord VARCHAR(64) SET @SearchWord = :search_term -- Using parameterized query for the search term SELECT TOP 400 w.SKU, w.PART_NO, w.SAYAL_PART_NO, w.SUB_PART_NO, w.CAT1, w.DESCRIPTION, w.DESCRIPTION2, w.ON_ORDER, w.LIST_P, w.REG_P, w.ALL_TOT, w.ON_HAND, w.QTY2, w.QTY3, w.QTY4, w.QTY5, w.QTY6, w.QTY7, w.QTY8, w.ACTION, w.REGULAR, m.COMPANY_NAME, w.SF, w.C, w.MFG, w.VENDOR, w.CIMG2, w.CLASS, w.SUB_CLASS, w.DFLAG, w.LFLAG, w.PERBAG, w.CDESCR, c.COMPANY_NAME, b.ID , b.CLASS_DESCR, b.SUB_CLASS_DESCR, b.GIDX, SPY.SHOPIFY, (w.ALL_TOT - w.ON_HAND) AS StoreQTY, d.WEIGHT, CASE WHEN w.SFLAG = 'N' THEN 'N' ELSE 'Y' END AS SFLAG, Specs.MEMO, Specs.MEMO1 FROM webinv3 AS w LEFT JOIN BIGINDEX ON w.SKU = BIGINDEX.SKU LEFT JOIN Sclassmast AS b ON (w.CLASS = b.CLASS) AND (w.SUB_CLASS = b.SUB_CLASS) LEFT JOIN MFR_DIST_LINKS AS c ON w.MFG = c.MFR LEFT JOIN Specs ON w.SKU = Specs.SKU LEFT JOIN MFR_DIST_LINKS AS m ON w.MFG = m.MFR LEFT JOIN Dimensions AS d ON w.SKU = d.SKU LEFT JOIN ( SELECT ZY.SKU, 'Y' AS SHOPIFY FROM webinv3 as ZY WHERE ZY.SFLAG <> 'N' AND ZY.SF not like 'EX' /*AND ZY.mfg not like 'mmFLUKE'*/ AND ZY.mfg not like 'CHANNL' AND ZY.mfg not like 'BEATS' /*AND ZY.CDESCR not like '%REFURB%' */ AND ZY.ALL_TOT > 0 ) AS SPY ON w.SKU = SPY.SKU WHERE (w.ALL_TOT > 0 OR (w.ALL_TOT = 0 AND w.ON_ORDER > 0)) AND CONTAINS(BIGINDEX.exp, @SearchWord) ORDER BY -- Prioritize items where 'wire' appears first in the description or part number CASE WHEN CHARINDEX('wire', w.DESCRIPTION) > 0 THEN CHARINDEX('wire', w.DESCRIPTION) ELSE 9999 END, CASE WHEN CHARINDEX('wire', w.PART_NO) > 0 THEN CHARINDEX('wire', w.PART_NO) ELSE 9999 END, w.SFLAG DESC, w.CAT1 ASC, w.SAYAL_PART_NO ASC, w.ALL_TOT DESC

ID	SKU	Searchterm