We built a piece of dynamic sql that generates a wide view from data in long format. Seen here:
CREATE PROCEDURE `selectPivotedTermpoints`(studyid varchar(300))
BEGIN
SET SESSION group_concat_max_len = 10000000;
SET @psql = NULL;
SET @finalSQL = NULL;
SET @StudyID = studyid;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(CASE WHEN terminate = ''', REPLACE(Terminate,'''', ''''''), ''' THEN 1 ELSE 0 END) AS `', REPLACE(Terminate,'''', ''), '`')
) INTO @psql
FROM Dashboard
WHERE studyid = @StudyID
AND completion_status = 'terminate';
SET @finalSQL = CONCAT('
SELECT Sample_provider as Provider,
completion_status as `Status`,',
@psql,'
FROM Dashboard
WHERE studyid = ''', @StudyID, '''
AND completion_status = ''terminate''
GROUP BY Sample_provider');
SELECT @finalSQL;
PREPARE stmt FROM @finalSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
When the sql is run as a query,(from SET to DEALLOCATE)setting @StudyID manually, we return a table with only the columns for that specific study(distinct Terminate as columns for only that study), however when the query is turned into a stored procedure and run it is generating a table with columns for all studies(all distinct Terminate as columns).
It appears that the first where clause (in the select group_concat) is being ignored when run as a stored procedure, but this is not the case when run as a simple query.
Stored procedure call:
selectPivotedTermpoints('bhp_03a');
Does anyone know why this is the case and / or how I can correct the issue?
I helped someone with a similar issue recently in another question; it confused us for quite a while. Change the parameter name to something else, I am guessing that WHERE is using it instead of the field in the table.
(You might be able to get away with Dashboard.studyid as well, but changing the parameter name will cause less confusion; and I am not positive how the query in @finalSQL would behave either.)