MySQL variable contents

Question

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.

Are you using MySQL or MS SQL Server? Don't tag products not involved. — jarlh
– jarlh, Commented Nov 15, 2016 at 10:38
What do you mean by "when I select this"? You select @sql_q1 or execute the statement? — FDavidov
– FDavidov, Commented Nov 15, 2016 at 10:40
@jarlh, MySQL is the DBMS and sql is the language. No conflict. — FDavidov
– FDavidov, Commented Nov 15, 2016 at 10:41
@FDavidov, if you check the edit history you will find an sql-server tag that was later removed - after I wrote my comment. — jarlh
– jarlh, Commented Nov 15, 2016 at 10:56

axiac · Accepted Answer · 2016-11-15 10:57:34Z

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;

Collectives™ on Stack Overflow

MySQL variable contents

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related