1

I am working on stored proceudes in mysql.

I found that for some of proc i have to write a same portion of sql query, But i need a way to reuse the same query code(not result) in all proc, How i can do that?

Here is my S.proc:

BEGIN
    SELECT date, sum(size_kg) as `total_size`, sum(net_size) as `total_net_size`,sum(bags) as `total_bags` FROM `stock` WHERE date=CURDATE() GROUP By CURDATE();
END

The code before where clause will be same in all proc, So How i can reuse that query portion code (Not result) ?

Hasnain Abid Khanzada
  • 21
  • 4
  • you could make a procedure (or function, if necessary) which is called by all the others. Just like a function/method in other languages. You might need to select the result into a temp table so it can be accessed by the calling procedure. – ADyson Dec 11 '18 at 15:27
  • Or...since this procedure is really just a single basic SELECT, you might be better off simply re-implementing it as a View – ADyson Dec 11 '18 at 15:29
  • +Adayson, can u explain it more? Because a stored procedure will return the result not code as I have mentioned in the description. – Hasnain Abid Khanzada Dec 11 '18 at 15:29
  • well it's the same thing isn't it? If you run the other procedure, you get the result. That's the identical outcome to if you actually _include_ the code in another procedure - it still executes, you still get access to the result. It would be the same in something like PHP - you can call a function from another function...it doesn't cause the code to be copied, but it gives you the ability to re-use the functionality, and get access to the result. That's the entire purpose of functions in programming - to stop you having to physically copy the same code in lots of places. – ADyson Dec 11 '18 at 15:31

1 Answers1

0

Since this particular procedure seems to be just a single, straightforward SELECT statement, the easiest thing to make it re-usable from multiple other places would simply be to turn it into a View instead.

If, as you say, the WHERE clause could be different depending on where the code is used, then simply omit the WHERE clause from the View, and let each caller set its own WHERE clause to restrict the data returned by the view to whatever it needs to be.

ADyson
  • 57,178
  • 14
  • 51
  • 63