Cannot create a stored procedure

SQL
1

In the Complete SQL Mastery course, under the Stored Procedures section, 2- Creating a Stored Procedure:

I was able to successfully create the stored procedure provided in the lesson called: get_clients()

But, for some reason, creating another stored procedure does not work. In this case, I tried to create the procedure listed in the code below, used for the Exercise part of the lesson. But nothing happens when I run it. The stored procedure is not created and no error message appears in the Output window. Can someone help me understand what I’m doing wrong, please?

DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
SELECT *
FROM invoices_with_balance
WHERE balance > 0;
END$$

DELIMITER ;

2

Remember to update the views. Mosh does it everytime a new View or Procedure is created.

Also check if there are errors, and share them.

3

Ah, I figured it out! The problem was with the comments I added. My example (above) doesn’t show them, but here’s what my code actually looked like (using double dashes):

DELIMITER $$ – resets the default delimiter to $$ instead of ;
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
SELECT *
FROM invoices_with_balance
WHERE balance > 0;
END$$

DELIMITER ; – sets the default delimiter back to ;

…so, MySQL doesn’t like those comments!! Yet. it did NOT report any ERRORS???

Anyway, after removing the 2 comments above, the stored procedure was created successfully!

1 Like
4

To avoid problems, put your comments on a new line.

DELIMITER $$ 
-– resets the default delimiter to $$ instead of ;
CREATE PROCEDURE get_invoices_with_balance()...
5

That’s awesome. Thanks, NicolasATC!

1 Like