The Microsoft SQL full-text indexing seems
to be working fine, however why does it only
work when I insert a value and it fails to
work when I insert the @txt varaible like
the original StoredProc below ?

Replaced "name like '%' + @txt + '%'" with
1. FREETEXT(NAME, 'ginger') --> works
2. FREETEXT(NAME, 'txt') --> fails
3. FREETEXT(NAME, @txt) --> fails
4. FREETEXT(NAME, '@txt') --> fails
------------------------------------------
** Originial **
CREATE PROCEDURE p_SEARCH_PRODUCTS
@TXT VARCHAR(500)
AS

DECLARE @today DECIMAL

set @today = DBO.DATETOMS(GETDATE())
SELECT
0 CATEGORY_ID,
P.PRODUCT_ID,
0 CAT_COUNT,
P.NAME,
P.SHORT_DESC,
P.THUMBNAIL,
MIN(S.PRICE) PRICE,
P.NAME_EXT,
0 PACKAGE_FLAG
INTO #OUT

FROM
PRODUCT P,
SKU S
WHERE
P.PRODUCT_ID IN (
select PRODUCT_ID from product where
name like '%' + @txt + '%'
)
AND P.PRODUCT_ID=S.PRODUCT_ID
AND @today between P.DATE_ACTIVATE AND P.DATE_DEACTIVATE
AND P.ACTIVE = 1 AND (P.DELETE_FLAG = 0 OR P.DELETE_FLAG IS NULL)
GROUP BY P.PRODUCT_ID,P.NAME,P.SHORT_DESC,P.THUMBNAIL,P.NAM E_EXT

UPDATE #OUT SET PACKAGE_FLAG = 1 WHERE PRODUCT_ID IN (
SELECT PRODUCT_GROUP_PRODUCT_ID FROM PRODUCT_GROUP G WHERE
PRODUCT_GROUP_SUB_TYPE_ID IN (
SELECT PRODUCT_GROUP_SUB_TYPE_ID FROM PRODUCT_GROUP_SUB_TYPE WHERE
PRODUCT_GROUP_TYPE_ID =1)
) -- 1 IS QUALITIES USED FOR PACKAGE OPTIONS
OR PRODUCT_ID IN(
SELECT PRODUCT_ID FROM PRODUCT_GROUP G WHERE PRODUCT_GROUP_SUB_TYPE_ID IN (
SELECT PRODUCT_GROUP_SUB_TYPE_ID FROM PRODUCT_GROUP_SUB_TYPE WHERE
PRODUCT_GROUP_TYPE_ID =1)
AND PRODUCT_ID <> PRODUCT_GROUP_PRODUCT_ID
)

SELECT top 100 * FROM #OUT
GO