[SOLVED] Where clause not working in stored procedure, when working outside of it

Where clause not working in stored procedure, when working outside of it

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?

Solution

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.)