• 0

MySQL - dynamic SQL ORDER BY problem


Question

This is my stored procedure for a search...

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?

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.