다음과 같이 조회하면 동적으로 사용할 수 있다.

DELIMITER $$

USE `dbname`$$

DROP PROCEDURE IF EXISTS usp_proc_select$$

CREATE DEFINER=`user`@`%` PROCEDURE `usp_proc_select`(

IN `iTemplate` varchar(128),

IN `iDeptName` varchar(64),

IN `iPageOffset` int,

IN `iPageSize` int,

IN `iOrderStr` varchar(128),

OUT `oTotalCount` int

)

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT '프로시저로 조회'

BEGIN

SET iTemplate = IFNULL(iTemplate, '');

SET iDeptName = IFNULL(iDeptName, '');

SET iPageOffset = IFNULL(iPageOffset, 0);

SET iPageSize = IFNULL(iPageSize, 10);

SET iOrderStr = IFNULL(iOrderStr, 'Name ASC');

SELECT SQL_CALC_FOUND_ROWS *

FROM (

SELECT

t.Id, t.Name

, COUNT(distinct dm.DeptId) AS DeptCount

FROM Template AS t

LEFT JOIN Template_Dept_Map AS dm ON t.Id = dm.TemplateId

WHERE 1 = 1

AND (CASE WHEN iTemplate = '' THEN 1 ELSE t.Name Like CONCAT('%', iTemplate, '%') END)

AND (CASE WHEN iDeptName = '' THEN 1 ELSE d.Name Like CONCAT('%', iDeptName, '%') END)

GROUP BY t.Id, t.Name, t.isBuiltIn

) AS a

ORDER BY isBuiltIn DESC,

CASE WHEN iOrderStr = 'Name ASC' THEN `Name` END ASC,

CASE WHEN iOrderStr = 'Name DESC' THEN `Name` END DESC,

CASE WHEN iOrderStr = 'DeptCount ASC' THEN DeptCount END ASC,

CASE WHEN iOrderStr = 'DeptCount DESC' THEN DeptCount END DESC

LIMIT iPageOffset, iPageSize;

SET oTotalCount := FOUND_ROWS();

END$$

DELIMITER ;

[MySQL & MariaDB] 동적 조회 프로시저 Dynamic Where