mysql stored procedure question

Question

I have a table with a credit and debit column.

I need to get the highest balance out of that, and I think a stored procedure is the way to do it, but I have no idea how.

I need to start with the first row, add the debits, subtract the credits and store the value A.

Second row is A+debit-credit=B; A = max(A,B) Repeat last step till the end.

Remember, I'm looking for the highest EVER, not the current, which would just be sum(debit-credit)

Answer 1

May

select max(debit-credit) from yourtable

work?

Try to test it.

Answer 2

It seems to me that you'd want the running total to be A+credit-debit but switch them if needed.

SET @balance := 0;
SET @high := 0;
SELECT @high := GREATEST(@balance := @balance+credit-debit, @high) FROM mytable;
SELECT @high;

edit: In response to your comment about forming a stored function from this... unlike stored procedures, stored functions must return one value, so they can't contain a SELECT query unless the query stores its result in a variable. This means the query must be guaranteed to have a single-value result. Below is a function I got to work, because in this case you only want the MAX value of @high anyway:

CREATE FUNCTION high_bal() RETURNS DECIMAL
BEGIN
  SET @balance := 0;
  SET @high := 0;

  SELECT MAX(@high := GREATEST(@balance := @balance+debit_acc-credit_acc, @high))
  INTO @high
  FROM credit_acc where credit_used_acc=63395;

  RETURN @high;
END$$

Answer 3

Actually, I thought I was going to be smart and make it a function, because I only want one value as the result...

DELIMITER $$

DROP FUNCTION IF EXISTS cc.high_bal$$

CREATE FUNCTION cc.high_bal() RETURNS DECIMAL

BEGIN
SET @balance := 0;
SET @high := 0;
SELECT @high := GREATEST(@balance := @balance+debit_acc-credit_acc, @high) FROM credit_acc where credit_used_acc=63395;

RETURN @high;
END$$

print("code sample");

DELIMITER ;

But that gives me the error " Not allowed to return a result set from a function" Which I don't understand because I do just want the single value.

Answer 4

User Defined Functions are only things that you embed within an SQL statement. They operate in the context of each single row (e.g. 40 rows, the function is executed 40 times). If you want an aggregate from a set (multiple rows), you use an SQL statement with an aggregate function in it (e.g. MAX(columname)). And that returns a "result set", which happens to be one row with one value.

Answer 5

Could anyone help me please - I need the below code in stored procedure format,its too urgent i have a deadline

function scormtrack($user,$courid) { $scormfile= get_records_sql('SELECT * FROM mdl_grade_grades gg, mdl_grade_items gi, mdl_scorm sc, mdl_scorm_scoes_track ssc WHERE gg.userid ='.$user.' AND usermodified ='.$user.' AND gi.courseid ='.$courid.' AND gi.id = gg.itemid AND sc.id = gi.iteminstance AND ssc.scormid = sc.id AND ssc.userid ='.$user.'');

$sq='SELECT * FROM mdl_grade_grades gg, mdl_grade_items gi, mdl_scorm sc, mdl_scorm_scoes_track ssc WHERE gg.userid ='.$user.' AND usermodified ='.$user.' AND gi.courseid ='.$courid.' AND gi.id = gg.itemid AND sc.id = gi.iteminstance AND ssc.scormid = sc.id AND ssc.userid ='.$user.''; echo $sq; $sq='SELECT * FROM mdl_grade_grades gg, mdl_grade_items gi, mdl_scorm sc, mdl_scorm_scoes_track ssc WHERE gg.userid ='.$user.' AND usermodified ='.$user.' AND gi.courseid ='.$courid.' AND gi.id = gg.itemid AND sc.id = gi.iteminstance AND ssc.scormid = sc.id AND ssc.userid ='.$user.''; echo $sq;

foreach($scormfile as $scormval) {

  $userid=$scormval->userid;
  $scormid=$scormval->scormid;
  $attempt=$scormval->attempt;
  $element=$scormval->element;
  $value=$scormval->value;
  $timemodified=$scormval->timemodified;

  $scoid=$scormval->scoid;

 $sqlabc="INSERT INTO mdl_scorm_scoes_tract_bk  (userid,scormid,scoid,attempt,element,value,timemodified) values($userid,$scormid,$scoid,$attempt,'$element','$value',$timemodified)";
 echo $sqlabc;
  $myquresult = mysql_query($sqlabc ) or die ('MySQL Error processing courses' . mysql_error());

  $delsql='DELETE FROM mdl_scorm_scoes_track where userid='.$userid.' AND scormid='.$scormid.'';

  echo $delsql;
  $myquresult = mysql_query($delsql ) or die ('MySQL Error processing courses' . mysql_error());

}

}