CODEWITHSUNDEEP
mysqlsqlmariadb
Mike
Mike

Reputation: 357

MySQL returns all select instead of output variable

Found extremely simple example which will represent this behaviour:

DECLARE Existing INT DEFAULT 0;
SELECT SQL_CALC_FOUND_ROWS name FROM users WHERE id = pid LIMIT 1;
SELECT FOUND_ROWS() INTO Existing;
Select Existing;

This will return both selects, instead of just showing Existing (same for output variables).

I have a stored procedure that has hundreds selects, I want to only retrieve one information about the user which is decided based on all those different selects, etc.

I do not wish to get 3MB of useless data from DB, I just want 16 byte return, nothing else.

Is it possible with MySQL ? Some time I've decided to move to MySQL, and we've made a lot of preparation for architecture, servers, etc. a lot of months of work.

Now I want to transfer the stored procedure, but seems based on all other questions, that MySQL doesn't care about lean approach and returns all that data making stored procedures pretty much useless (at least when you've worked with big SQL Server databases using stored procedures / functions and everything work as you would expect, keep all the internal data, selects, etc. where it belongs, do not return it to the client).

I've found info that it should be supported by a client, but it is against any kind of programming best practices (i.e. sending MB od useless data between servers while you only need 4 bytes, main reason to use stored procedures instead of writing the code in i.e. C# (as you would have to make hundreds of calls to DB to get i.e. account overview)).

Am I missing something ?

I want to call stored procedure, that can make hundreds of calls to other stored procedure, process i.e. 100MB of data, and the return only output variables (i.e. 5 that providing overview of the account)...

We also have AI engine that is still using SQL Server, and in there well you cannot afford getting dozes of MB per entity if you have to do it millions of times per day. It would break the whole application...

Please only tell me if MySQL can output stored procedure result to selected variables. I do not care about megabytes of useless data to me. I am not asking about counts or anything else. I am talking about complicated logic, and I want to know only if MySQL can do what Microsoft SQL Server does? (Stored procedure returns output ant that is it, that is why you have output parameter, this is why stored procedure exists)

Upvotes: 0

Views: 194

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Why are you doing the select if you don't want the results? A simpler way to get the results is to use count(*):

DECLARE Existing INT DEFAULT 0;
SELECT COUNT(*) INTO Existing FROM users WHERE id = pid;

SQL_CALC_FOUND_ROWS and FOUND_ROWS() are specifically for the situation where you want the query to return results.

Upvotes: 1

Related Questions