CODEWITHSUNDEEP
mysqlstored-procedures
user2129506
user2129506

Reputation: 131

Computation in MySQL

I want to get the results of a calculation in a stored procedure which admits only one parameter ("code") in example.

delimiter $$
create procedure Test(in code varchar(8)) language sql deterministic
begin
set @sql=concat("select (a+b+c) as N, (d+e+f) as D from Tbl where code='",code,"'");
set r=N/D;
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
set @age=@NUM/@DEN;
end$$

I would like to get N, D and r.

Upvotes: 0

Views: 74

Answers (1)

Carsten Massmann
Carsten Massmann

Reputation: 28206

It seems to me that you might solve your problem more easily with a view instead of a stored procedure. You could, for example define the view 

CREATE VIEW testview AS
SELECT *,N/D r FROM (select code, a+b+c N, d+e+f D FROM tmp) sub;

When you use that in a SELECT then you can easily filter for the code you are interested in, like

SELECT * from testview WHERE code='yourcode'

You will then get a result like

code      N    D   r
yourcode  468  234 2

The view can of course be used in connection with other, more complex queries or subqueries.

Edit:
As hinted by Paul the view will be much more effective if written without the use of a subquery:

CREATE VIEW testview AS
SELECT code, a+b+c N, d+e+f D, (a+b+c)/(d+e+f) r  FROM tmp;

Upvotes: 2

Related Questions