CREATE PROCEDURE get_product_range (
IN _TEXT VARCHAR(255),
IN _CATID VARCHAR(255),
IN _MFRID VARCHAR(255),
IN _ORDERBY VARCHAR(255),
IN _START INTEGER,
IN _LIMIT INTEGER
)
BEGIN
PREPARE STMT FROM "
SELECT
WS.T_PRODUCT__PRODUCT_ID AS 'ID',
WS.T_PRODUCT__CATALOGUE_ID AS 'MRNumber',
WS.ARTIST__NAME AS 'name',
WS.LABEL__NAME AS 'manufacturer',
WS.GENRE__DESCRIPTION AS 'category',
WS.T_PRODUCT__DESCRIPTION AS 'shortDescription',
WS.T_PRODUCT__INTERNET_COMMENTS AS 'internalOverview',
WS.T_PRODUCT__TRACK_LISTING_TECH_SPEC AS 'specifications',
P.SELLING_PRICE AS 'price',
MATCH
(
WS.T_PRODUCT__CATALOGUE_ID,
WS.T_PRODUCT__CATALOGUE_ID_NUMERIC,
WS.T_PRODUCT__DESCRIPTION,
WS.T_PRODUCT__INTERNET_COMMENTS,
WS.T_PRODUCT__TRACK_LISTING_TECH_SPEC,
WS.LABEL__NAME,
WS.ARTIST__NAME,
WS.FORMAT__CODE,
WS.ORGANISATION__NAME,
WS.GENRE__DESCRIPTION,
WS.CATEGORY__NAME
)
AGAINST
(
?
) AS 'Match',
WS.CATEGORY_ID AS 'categoryID',
WS.Label_ID AS 'manufacturerID'
FROM
t_websearch WS
JOIN
t_product P ON (P.PRODUCT_ID = WS.T_PRODUCT__PRODUCT_ID)
WHERE
MATCH
(
WS.T_PRODUCT__CATALOGUE_ID,
WS.T_PRODUCT__CATALOGUE_ID_NUMERIC,
WS.T_PRODUCT__DESCRIPTION,
WS.T_PRODUCT__INTERNET_COMMENTS,
WS.T_PRODUCT__TRACK_LISTING_TECH_SPEC,
WS.LABEL__NAME,
WS.ARTIST__NAME,
WS.FORMAT__CODE,
WS.ORGANISATION__NAME,
WS.GENRE__DESCRIPTION,
WS.CATEGORY__NAME
)
AGAINST
(
?
)
AND
WS.CATEGORY_ID LIKE ?
AND
WS.LABEL_ID LIKE ?
ORDER BY
?
LIMIT ?,?
";
SET @TEXT = _TEXT;
SET @CATID = _CATID;
SET @MFRID = _MFRID;
SET @ORDERBY = _ORDERBY;
SET @START = _START;
SET @LIMIT = _LIMIT;
IF @CATID = '' OR @CATID IS NULL THEN
SET @CATID = '%';
END IF;
IF @MFRID = '' OR @MFRID IS NULL THEN
SET @MFRID = '%';
END IF;
IF @ORDERBY = '' OR @ORDERBY IS NULL THEN
SET @ORDERBY = 'price';
END IF;
EXECUTE STMT USING @TEXT, @TEXT, @CATID, @MFRID, @ORDERBY, @START, @LIMIT;
END;
Nice and simple - I am being forced to use DynamicSQL statements due to a LIMIT issue withing stored procedures. Ignore that, it's all fine...
It is called by: CALL get_product_range('dj','1','2752','name',0,100)
However, it is not ORDERING by the correct field. It's fine if I hard-code the field, but not when it's being sent as a parameter. Any help?
Question
+Dick Montage Subscriber²
This is my stored procedure for a search...
Nice and simple - I am being forced to use DynamicSQL statements due to a LIMIT issue withing stored procedures. Ignore that, it's all fine...
It is called by: CALL get_product_range('dj','1','2752','name',0,100)
However, it is not ORDERING by the correct field. It's fine if I hard-code the field, but not when it's being sent as a parameter. Any help?
Link to comment
Share on other sites
1 answer to this question
Recommended Posts