CODEWITHSUNDEEP
mysqlsql
user1156596
user1156596

Reputation: 601

MySQL variable contents

When selecting a MySQL variable which contains a concatenation of a select statement and another variable like so:

SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile,
visit.id FROM carecube.visit order by visit.id LIMIT 1 OFFSET ', @var, ')'));

Why so, when I select this, why does it return the select statement as text rather than a value?

Thanks.

EDIT: When I run the following select statement to select the variable, it just prints the select statement within the variable and prints out

'SELECT "Q1" AS quartile, visit.id FROM carecube.visit order by visit.id LIMIT 1 OFFSET 58'

I assumed I would get a number returned, offsetting after the 58th row and not the text.

Upvotes: 0

Views: 61

Answers (1)

axiac
axiac

Reputation: 72177

You generate a string in a variable (@sql_q1). Incidentally, your string is a MySQL query but this doesn't make it special in any way.

When you run SELECT @sql_q1; you get the content of the variable (the string) and nothing else.

In order to tell MySQL to interpret the content of the variable (i.e. the string) as a query you can use it as a prepared statement.

# Generate the query in a variable
SET @sql_q1 := CONCAT(
    'SELECT "Q1" AS quartile, visit.id FROM carecube.visit order by visit.id LIMIT 1 OFFSET ',
    @var
);

# Use the content of the variable to create a prepared statement
PREPARE stmt1 FROM @sql_q1;

# Run the prepared statement
EXECUTE stmt1;

# Cleanup
DEALLOCATE PREPARE stmt1;

You don't even need to generate a fixed query; you can use placeholders for values and provide values for them on the EXECUTE statement:

SET @sql_q2 := 'SELECT "Q1" AS quartile, visit.id FROM carecube.visit order by visit.id LIMIT 1 OFFSET ?';
PREPARE stmt2 FROM @sql_q2;
EXECUTE stmt2 USING @var;
DEALLOCATE PREPARE stmt2;

Upvotes: 1

Related Questions