0

I'm trying to use a dynamic SQL statement to retrieve a value and assign it to a variable, in a stored procedure.

The problem is, when executing the dynamic statement, it can't access the variable declared in the procedure scope.

Here is a fragment of the code (inside a procedure), for clarification:

DECLARE v_amount_of_samples_that_require_revision INTEGER;

SET query = CONCAT('SELECT count(sample_id) INTO v_amount_of_samples_that_require_revision
        FROM ', v_table_name,
        'WHERE state = REQUIRES_REVISION_STATE
        AND form_id = ', p_form_id);                    

PREPARE stmt FROM query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The error I get, of course, is:

SQL Error [1327] [42000]: (conn:7575) Undeclared variable: v_amount_of_samples_that_require_revision

Is there a workaround?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Martín Lehoczky
  • 98
  • 5

1 Answers1

2

instead of regularly variables use user defned session variables

still v_table_name could be used for sql injection and should be tested against a whitelist

When you use variables in the where lause use prepared statements

SET @query = CONCAT('SELECT count(sample_id) INTO @v_amount_of_samples_that_require_revision
        FROM ', v_table_name,
        ' WHERE state = REQUIRES_REVISION_STATE
        AND form_id = ?');                    
SET @p_form_id =  p_form_id;
PREPARE stmt FROM @query;
EXECUTE stmt USING @p_form_id;
DEALLOCATE PREPARE stmt;
SELECT @v_amount_of_samples_that_require_revision;
nbk
  • 45,398
  • 8
  • 30
  • 47