一个存储过程的优化问题，请大家帮帮忙。

[lnfmly]

CREATE PRPCEDURE PRO_SELECT_SHOP(IN _typeId INT,IN _address varchar(200), IN _orderByList VARCHAR(255), IN _startRecord int, IN _maxRecord int)
BEGIN
set @address = _address;
set @typeId = _typeId;
SET @SqlCmd = "select s.shop_id, s.shop_name, s.address, s.phone, s.mobile_telephone, s.type_id, s.register_time, t.type_name from (select shop_id, shop_name, address, phone, mobile_telephone, type_id, register_time from shop";
IF @typeId IS NOT NULL AND @typeId > 0 THEN
SET @SqlCmd = CONCAT(@SqlCmd, " where type_id = ?");
IF @address IS NOT NULL AND LENGTH(@address) > 0 THEN
SET @SqlCmd = CONCAT(@SqlCmd, " and address like ?");
END IF;
ELSE
IF @address IS NOT NULL AND LENGTH(@address) > 0 THEN
SET @SqlCmd = CONCAT(@SqlCmd, " where address like ?");
END IF;
END IF;
SET @SqlCmd = CONCAT(@SqlCmd, ") as s join (select type_id, type_name from shoptype where type_id in (select type_id from shop");
IF @address IS NOT NULL AND LENGTH(@address) > 0 THEN
SET @SqlCmd = CONCAT(@SqlCmd, " where address like ?");
END IF;
SET @SqlCmd = CONCAT(@SqlCmd, ")) as t on t.type_id = s.type_id");
IF _orderByList IS NOT NULL AND LENGTH(_orderByList) > 0 THEN
SET @orderByList = _orderByList;
SET @SqlCmd = CONCAT(@SqlCmd, " order by ?");
END IF;
IF _startRecord IS NOT NULL AND _maxRecord IS NOT NULL THEN
SET @startRecord = _startRecord;
SET @maxRecord = _maxRecord;
SET @SqlCmd = CONCAT(@SqlCmd, " limit ?,?");
END IF;

PREPARE stmt FROM @SqlCmd;
IF _typeId IS NOT NULL THEN
IF _address IS NOT NULL THEN
IF _startRecord IS NOT NULL AND _maxRecord IS NOT NULL THEN
EXECUTE stmt USING @typeId, @address, @address, @orderByList, @startRecord, @maxRecord;
ELSE
EXECUTE stmt USING @typeId, @address, @address, @orderByList;
END IF;
ELSE
IF _startRecord IS NOT NULL AND _maxRecord IS NOT NULL THEN
EXECUTE stmt USING @typeId, @orderByList, @startRecord, @maxRecord;
ELSE
EXECUTE stmt USING @typeId, @orderByList;
END IF;
END IF;
ELSE
IF _address IS NOT NULL THEN
IF _startRecord IS NOT NULL AND _maxRecord IS NOT NULL THEN
EXECUTE stmt USING @address, @address, @orderByList, @startRecord, @maxRecord;
ELSE
EXECUTE stmt USING @address, @address, @orderByList;
END IF;
ELSE
IF _startRecord IS NOT NULL AND _maxRecord IS NOT NULL THEN
EXECUTE stmt USING @orderByList, @startRecord, @maxRecord;
ELSE
EXECUTE stmt USING @orderByList;
END IF;
END IF;
END IF;
END

我想把它做得通用一点，前面都好可以接受，可是在后面调用执行的时候要判断这么多的参数我觉得太麻烦了
请教大家有什么办法可以优化一下，或者用什么别的方法解决？
把这些方法分开写我又觉得太多了，没有必要？
在调用EXECUTE 的时候有没有办法可以简化一些呢？

[lnfmly]

[Quote=引用 1 楼 yyz0832 的回复:]
建议不要使用EXECUTE stmt USING @typeId, @address, @address, @orderByList; 这样的形式，可是根据参数直接拼成sql语句，这样直接 execute stmt 就可以了
[/Quote]
可是 用这样带“？”的参数方式不是效率更高吗？

[yyz0832]

建议不要使用EXECUTE stmt USING @typeId, @address, @address, @orderByList; 这样的形式，可是根据参数直接拼成sql语句，这样直接 execute stmt 就可以了