I'm need to the procedures in MySQL.... I'm trying to build dynamic SQL, similar to MS SQL Server. Can anyone tell me what's wrong with this procedure?

DELIMITER $$

DROP PROCEDURE IF EXISTS `goes_modules`.`RPT_WorkForcePlan_SP`$$ CREATE PROCEDURE `goes_modules`.`RPT_WorkForcePlan_SP`(IN Page int, IN RecsPerPage int, IN Sort varchar(25), IN WhereClause varchar(2000), OUT RecordCount varchar(25)) BEGIN DECLARE FirstRec varchar(10); DECLARE LastRec varchar(10); DECLARE CurrYear varchar(4); DECLARE SQL1 varchar(2000); DECLARE SQL2 varchar(2000); DECLARE RecordCountMsg varchar(100);

-- Select CurrYear=curdate(YYYY);

CREATE TEMPORARY TABLE TempItems (ID int AUTO_Increment, RequestID varchar(10), `Function` varchar(250), Organization varchar(250), Company varchar(250), FirstLastName varchar(500), Year1 int, Year2 int, Year3 int, Year4 int, Year5 int, Year6 int, Year7 int, Year8 int, Year9 int, Year10 int, FTETotal int);

SET SQL1 = 'INSERT INTO TempItems (RequestID,`Function`, Organization,Company,FirstLastName, ' SET SQL1 = SQL1 + 'Year1,Year2,Year3,Year4,Year5,Year6,Year7,Year8, Year9,Year10,FTETotal) ' SET SQL1 = SQL1 + 'select distinct r.requestid, function, organization,company, ' SET SQL1 = SQL1 + 'IFNULL(CAST((select group_concat(rtrim(ww. firstlast) SEPARATOR '', '') from workforceplan_workers ww where ww.requestid = r.requestid) AS CHAR),'''') AS `FirstLastName` ' SET SQL1 = SQL1 + ',sum(CASE year WHEN ''2006'' THEN fte ELSE 0 END) as ''2006'' ' SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2007'' THEN fte ELSE 0 END) as ''2007'' ' SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2008'' THEN fte ELSE 0 END) as ''2008'' ' SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2009'' THEN fte ELSE 0 END) as ''2009'' ' SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2010'' THEN fte ELSE 0 END) as ''2010'' ' SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2011'' THEN fte ELSE 0 END) as ''2011'' ' SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2012'' THEN fte ELSE 0 END) as ''2012'' ' SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2013'' THEN fte ELSE 0 END) as ''2013'' ' SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2014'' THEN fte ELSE 0 END) as ''2014'' ' SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2015'' THEN fte ELSE 0 END) as ''2015'' ' SET SQL1 = SQL1 + ' ,IFNULL(sum(fte),0) as ftetotal ' SET SQL1 = SQL1 + ' from workforceplan_request r left join workforceplan_fte rf ' SET SQL1 = SQL1 + ' on r.requestid = rf.requestid left join workforceplan_workers sw on sw.requestid = r.requestid ' SET SQL1 = SQL1 + ' group by r.requestid, function,organization,company ' IF WhereClause <> '' SET SQL1 = SQL1 + ' WHERE ''''='''' ' + WhereClause END IF; IF Sort <> '' SET SQL1 = SQL1 + ' Order by ' + Sort END IF;

CALL SQL1;

SET FirstRec = (Page - 1) * RecsPerPage SET LastRec = Page * RecsPerPage + 1

SET RecordCount = count(*) from TempItems SET RecordCountMsg = Page + ' pages were requested, only '

SET SQL2 = 'SELECT * FROM TempItems WHERE ID > ' + FirstRec SET SQL2 = SQL2 + 'AND ID < ' +LastRec

CALL SQL2;

END$$

DELIMITER ;

Melissa