How would I assign the result of an sql query to a variable using mysql procedures?

Question

I have a table in mysql with n rows and columns

col1 col2
a     b
c     d

...

Using mysql procedures, how would I get all the rows into 1 varchar variable?

DECLARE var varchar(4096);
SET var = <select * from table>

The value of var should be "a b\nc d\n...."

Thank you.

Gordon Linoff · Accepted Answer · 2014-11-25 02:19:38Z

3

You could do:

select var := group_concat(col1, ' ', col2 separator '
')
from table;

Seems a bit strange, but it will do what you want.

EDIT:

You can also do:

select var := group_concat(col1, ' ', col2 separator '\n')
from table;

edited Nov 25, 2014 at 2:19

answered Nov 25, 2014 at 1:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user674669 Over a year ago

Can you please explain. What is 'separator'? Are you sure about the syntax? There are 2 quotes after separator - one of them is on a newline.

Gordon Linoff Over a year ago

@user674669 . . . Usually \n means a new line. That is an easy way to put one in, without worrying about escaping things. separator is a keyword for group_concat().

Collectives™ on Stack Overflow

How would I assign the result of an sql query to a variable using mysql procedures?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related