1

I have something like this:

DELIMITER $$

DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test(
    IN a_in varchar(3), 
    IN b_in varchar(3))
begin
    select a_in as a, b_in as b;
end $$
DELIMITER ;

CALL test('1', '2');

I do not want to modify the SP and make it write to a temporary table. I am trying to write some unit tests for the SP as it is written. How can I look a the result set after calling the SP? I want to look at the result set and do comparisons to ensure it returned what I was expecting. Something like this:

set  @var1, @var = (CALL test('1', '2');)
if @var1 <> 1 then
   select 'Error, @var1 should be 1';

I realize I would need to wrap that in a SP. I also realize that a SP can return multiple result sets and I need to get the first of the result sets and maybe assign that to a cursor. Is there a way to do this in MySQL? Otherwise, I need to do this in a different language (i.e. Python, PHP, etc), but, that adds another layer for what should be a unit test for the SP itself.

Doo Dah
  • 3,979
  • 13
  • 55
  • 74
  • So, you are writing mysql unit test, right?...the closest thing that I can think of is that stored procedures don't really return output directly. They can execute select statements inside the script, but have no return value. MySQL calls stored procedures via CALL StoredProcedureName(); And you cannot direct that output to anything, as they don't return anything (unlike a function)....check this answer: http://stackoverflow.com/questions/687102/mysql-how-to-insert-into-temp-table-from-stored-procedure – Hackerman Nov 15 '16 at 14:49
  • Yes. I want to write a unit test in SQL. But no, I do not want to change the implementation of the SP to write to a temporary table. Is there not a way to capture the result set from a SP? – Doo Dah Nov 15 '16 at 14:57
  • No, there is no way....only for functions...the other way is writing your unit test using a server side scripting language, like php – Hackerman Nov 15 '16 at 15:02

0 Answers0