CODEWITHSUNDEEP
mysqlstored-proceduresmariadb
passion053
passion053

Reputation: 387

Is there a way to set multiple variables in one SELECT query in MySQL?


Hello, everybody.

In my SP, there's a SELECT query like this.

SELECT val_01, val_02 INTO var_01, var_02 FROM table_name WHERE col_name = condition_01;

This query works properly.

But I've added exit handler for SQLWARNING and when above SELECT query has no result, it makes SQL WARNING like this and stopped.

No data - zero rows fetched, selected, or processed

I've googled and found a way to solve this problem. It is changing the query like this...

SELECT var_01 = val_01, var_02 = val_02 FROM table_name WHERE col_name = condition_01;

This query works. But there's a difference between original query and modified query.

In SP, original one doesn't throw SELECT query's result. It just put SELECT query's value into variables.

But the latter one throws its SELECT result with variable's value.

So my cpp code which call SP get wrong value.

I can change query to..

SET var_01 = SELECT val_01 FROM table_name WHERE col_name = condition_01;

SET var_02 = SELECT val_02 FROM table_name WHERE col_name = condition_01;

But as you know, this is very inefficient and below query doesn't work.

SET var_01, var_02 = (SELECT val_01, val_02 FROM table_name WHERE col_name = condition_01);

Is there an efficient way to make same result of original query?

Please help me. Thank you.

Upvotes: 1

Views: 8832

Answers (3)

Michael - sqlbot
Michael - sqlbot

Reputation: 179074

Without more context, it's unclear precisely what you are asking, but if you need to temporarily override the exit handler, nest your query in a new scoping block, with a handler to clear the variables. (They have to be cleared if inside a loop, or they will still contain their prior values if assigned).

-- inside existing procedure
BEGIN -- add this and the handler
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_01 = NULL, var_02 = NULL;
  SELECT val_01, val_02 INTO var_01, var_02 FROM table_name WHERE col_name = condition_01;
END; -- add this
-- procedure continues here

Upvotes: 2

Rick James
Rick James

Reputation: 142298

SELECT a, b INTO aa, bb ...

Reference: See INTO in https://dev.mysql.com/doc/refman/5.7/en/select.html

Upvotes: 1

Mil0R3
Mil0R3

Reputation: 3956

SET @var_01 = @var_02 = '';
SELECT _val_01, _val_02 INTO @var_01, @var_02 FROM table_name WHERE col_name = condition_01;

// check whether var_01=='' or length==0 then do what you want
SELECT @var_01, @var_02
// or 
SELECT LENGTH(@var_01)

Upvotes: 0

Related Questions