Nice, good that GetSimpleCustomers() works. Let us rebuild the whole explanation properly around that style and then add the other methods.

1. What is a stored procedure?

A stored procedure is a saved SQL program in the database.

• You define it once using CREATE PROCEDURE.
• After that, you call it with CALL procedureName(...).
• It lives inside the database (like a reusable script).

You use stored procedures to:

1. Reuse complex queries
2. Hide logic from application code
3. Enforce consistent business rules
4. Improve maintainability

2. The simple format that works in DbVisualizer

This is the format that worked for you:

CREATE PROCEDURE GetSimpleCustomers()
SELECT
    customerName,
    city,
    state,
    postalCode,
    country
FROM customers
ORDER BY customerName;

Key points:

1. No BEGIN and END
2. No DELIMITER changes
3. Exactly one SQL statement after CREATE PROCEDURE ...()

This style is valid in MySQL when the procedure body is just a single SQL statement.

2.1 Step by step in DbVisualizer using this format

1. Open SQL Commander in DbVisualizer

Tools → SQL Commander → New SQL Commander

2. Select the correct database

At the top, set the database to classicmodels or whichever you are using.

3. Paste your procedure:
Copy

CREATE PROCEDURE GetSimpleCustomers()
SELECT
    customerName,
    city,
    state,
    postalCode,
    country
FROM customers
ORDER BY customerName;

4. Execute the SQL

Use normal execute (Ctrl + Enter).

5. Confirm it exists

In the database tree, expand your database → Procedures.

You should see GetSimpleCustomers.

2.2 How to call the stored procedure

Once created, call it like this:

CALL GetSimpleCustomers();

or, with schema:

CALL classicmodels.GetSimpleCustomers();

Run that as a normal statement in SQL Commander.

You will see a result grid with your customers.

3. Adding parameters using the simple style

You can still use parameters with the simple format, as long as the body is a single statement.

Example: customers by country

CREATE PROCEDURE GetCustomersByCountry(IN p_country VARCHAR(50))
SELECT
    customerName,
    city,
    state,
    postalCode,
    country
FROM customers
WHERE country = p_country
ORDER BY customerName;

Call it like this:

CALL GetCustomersByCountry('USA');

Explanation:

• IN p_country VARCHAR(50) is an input parameter.
• You can use the parameter in the query just like a column or variable.

Another example: customers over a credit limit

CREATE PROCEDURE GetCustomersOverLimit(IN p_limit DECIMAL(10,2))
SELECT
    customerName,
    creditLimit,
    city,
    country
FROM customers
WHERE creditLimit > p_limit
ORDER BY creditLimit DESC;

Call:

CALL GetCustomersOverLimit(50000.00);

4. When do you need BEGIN and END?

You must move to the multi-statement style when you need:

1. More than one SQL statement in the procedure

example: SET variable then SELECT

2. Local variables

DECLARE v INT;

3. Flow control

IF, CASE, WHILE, LOOP, etc.

4. Output parameters that require setting values from logic

Example in standard MySQL syntax:

DELIMITER $$

CREATE PROCEDURE HelloUser()
BEGIN
    DECLARE v CHAR(10) DEFAULT 'Hello';
    SELECT CONCAT(v, ', ', CURRENT_USER(), '!');
END$$

DELIMITER ;

In normal MySQL clients (CLI, Workbench), this is fine.

In DbVisualizer, you need to be careful.

5. Multi-statement procedures in DbVisualizer

DbVisualizer gets confused by ; inside a procedure, because it usually treats it as the end of the statement.

There are two main approaches.

5.1 Using DELIMITER plus “Execute Buffer as Script”

Write your multi-statement procedure like this:

DELIMITER $$

CREATE PROCEDURE HelloUser()
BEGIN
    DECLARE v CHAR(10) DEFAULT 'Hello';
    SELECT CONCAT(v, ', ', CURRENT_USER(), '!');
END$$

DELIMITER ;

Important in DbVisualizer:

1. Put all of that in one SQL Commander buffer
2. Do not run line by line
3. Use “Execute Buffer as Script” (Ctrl + Shift + Enter)

That tells DbVisualizer to send the whole block correctly, including the delimiter changes.

Call it afterwards with:

CALL HelloUser();

5.2 Using DbVisualizer’s procedure editor

Some DbVisualizer versions have a procedure editor, which hides the delimiter issues.

Typical flow:

1. In the database tree, right click the schema

Choose Create → Procedure or similar

2. Paste this body:
Copy

CREATE PROCEDURE HelloUser()
BEGIN
    DECLARE v CHAR(10) DEFAULT 'Hello';
    SELECT CONCAT(v, ', ', CURRENT_USER(), '!');
END

3. Click Execute in that dialog

DbVisualizer takes care of sending it correctly.

6. IN, OUT, and INOUT parameters

So far you used IN parameters. There are three types:

1. IN

Value is passed into the procedure. You cannot change it for the caller.

2. OUT

Procedure sets the value and returns it out.

3. INOUT

Caller passes a value in, procedure can modify it and pass back.

Simple OUT example:

DELIMITER $$

CREATE PROCEDURE CountCustomersByCountry(
    IN p_country VARCHAR(50),
    OUT p_count INT
)
BEGIN
    SELECT COUNT(*)
    INTO p_count
    FROM customers
    WHERE country = p_country;
END$$

DELIMITER ;

Calling from SQL:

SET @c := 0;
CALL CountCustomersByCountry('USA', @c);
SELECT @c AS total_customers_in_usa;

In DbVisualizer, you run this whole block as “Execute Buffer as Script”.

Use OUT and INOUT only when you really need result variables.

Most reporting-style procedures can just return a result set using SELECT.

7. Managing stored procedures

7.1 See the procedure definition

Use:

SHOW CREATE PROCEDURE GetSimpleCustomers;

This shows how MySQL actually stored it.

7.2 List procedures in a schema

SHOW PROCEDURE STATUS
WHERE Db = 'classicmodels';

Or just look in the DbVisualizer database tree under Procedures.

7.3 Drop a procedure

If you want to recreate or remove it:

DROP PROCEDURE IF EXISTS GetSimpleCustomers;

Then create it again with new logic.

8. Practical summary for you

For your current learning and classicmodels work:

1. Prefer the simple pattern in DbVisualizer when possible:
Copy

CREATE PROCEDURE ProcName(...)
SELECT ...;

2. Use IN parameters to make the procedure dynamic.
3. Call with CALL ProcName(...);
4. Move to BEGIN and END only when you need variables or multiple statements.
5. When using BEGIN and END, either:
• use DELIMITER plus “Execute Buffer as Script”, or
• use the DbVisualizer procedure editor.

If you like, next we can design two or three useful procedures specifically for classicmodels that support your finance and analytics learning, for example:

• top customers by sales
• late payments or high credit risk customers
• sales summary by country and year.