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
