2014/04/10 - [프로그램 자료/MS-SQL] - [MS-SQL]동적조회 조건에서의 정적 프로시져와 동적프로시져 Dynamic Where
2018/07/24 - [프로그램 자료/MySQL & MariaDB] - [MySQL & MariaDB] 동적 프로시저 dynamic procedure - 기간 포함
2018/08/08 - [프로그램 자료/MySQL & MariaDB] - [MySQL & MariaDB] 동적쿼리 사용시 in clause 사용하기
다음과 같이 조회하면 동적으로 사용할 수 있다.
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 ;
