Dynamic SQL is a technique that allow query to execute at runtime that is predefined in stored procedure based on condition and input parameter.
Here we will discuss about what is dynamic query and how to write dynamic query in Stored procedure with example.here i will show how to fetch employee data and some filter and pagination using dyanmic query in my sql stored procedure.
Let me show you an example
CREATE PROCEDURE `udsp_employee`(
_PageNo INT,
_PageSize INT,
_City VARCHAR(50)
)
BEGIN
DECLARE vOffSet INT DEFAULT 1;
DECLARE vQuery MEDIUMTEXT;
SET vOffSet = (_PageNo-1) * _PageSize;
SET @vRowCount=0;
SET vQuery=CONCAT('SELECT @vRowCount :=@vRowCount+1 as RowNumber, cpd.Id ClientId,cpd.Name ClientName FROM employee e
INNER JOIN `department` d ON e.EmployeeId=d.EmployeeId WHERE e.EmployeeId>0');
IF IFNULL(_City,'') <> '' THEN
SET vQuery=CONCAT(vQuery,' AND e.City="',_City,'"');
END IF;
SET vQuery=CONCAT(vQuery,' ORDER BY e.Id DESC');
SET vQuery=CONCAT('SELECT * FROM (SELECT * FROM (',vQuery,' )t LIMIT ',_PageSize,' OFFSET ',vOffSet,') t');
SET @vQueryMyLeads=vQuery;
PREPARE stmtMyLeads FROM @vQueryMyLeads; EXECUTE stmtMyLeads;DEALLOCATE PREPARE stmtMyLeads;
SELECT CEIL(@vRowCount) TotalRows,CEIL(@vRowCount/_PageSize) NoOfPages,_PageNo CurrentPage,_PageSize PageSize;
END;