Learn / SQL Server → MySQL / Stored Procedures
Lesson 4 of 5

Stored Procedures

This is where the two dialects diverge the most. T-SQL's procedural extensions and MySQL's are both "SQL plus a bit of imperative glue," but the glue is shaped differently enough that a stored procedure rarely ports by find-and-replace alone — the DELIMITER dance in particular catches almost everyone the first time.

💡 The Bottom Line

MySQL needs you to temporarily change the statement delimiter (usually to // or $$) before defining a procedure, because the procedure body itself contains semicolons that would otherwise end the CREATE PROCEDURE statement early. Variables are declared with DECLARE inside the body (not @name up front), and MySQL has no schema concept the way dbo. works in SQL Server — procedures live directly in a database/schema, one flat namespace.

Basic procedure shape

SQL Server (T-SQL)
CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId INT
AS
BEGIN
    SELECT OrderId, OrderDate
    FROM Orders
    WHERE CustomerId = @CustomerId;
END;
MySQL
DELIMITER //

CREATE PROCEDURE get_orders_by_customer(
    IN p_customer_id INT
)
BEGIN
    SELECT order_id, order_date
    FROM orders
    WHERE customer_id = p_customer_id;
END //

DELIMITER ;

📋 Key differences

  • SQL Server parameters are declared before AS, prefixed with @, and used the same way inside the body; MySQL parameters are declared inside the parentheses right after the procedure name, each tagged IN, OUT, or INOUT, with no @ prefix.
  • SQL Server wraps the body in BEGIN ... END optionally (a single statement doesn't strictly need it); MySQL's BEGIN ... END block is effectively mandatory for anything beyond a single statement.
  • dbo.GetOrdersByCustomer's dbo schema prefix has no MySQL equivalent — MySQL procedures live directly inside a database, referenced as mydb.get_orders_by_customer only when calling across databases.
  • Naming convention differs by culture more than by rule: T-SQL shops commonly use PascalCase; MySQL shops overwhelmingly use snake_case — neither engine enforces either.

⚠️ Gotcha

Forgetting DELIMITER // before a multi-statement procedure is the single most common first-timer error in MySQL. Without it, the client sees the first semicolon inside the procedure body (e.g. after the SELECT) and thinks that's the end of the whole CREATE PROCEDURE statement — you get a confusing syntax error complaining about END, because as far as the parser is concerned, END is now a stray keyword with no matching BEGIN in scope. Always switch the delimiter before, and switch it back to ; after.

Declaring variables

SQL Server (T-SQL)
CREATE PROCEDURE dbo.CountOrders
AS
BEGIN
    DECLARE @Total INT;
    SELECT @Total = COUNT(*) FROM Orders;
    SELECT @Total AS TotalOrders;
END;
MySQL
DELIMITER //

CREATE PROCEDURE count_orders()
BEGIN
    DECLARE total INT;
    SELECT COUNT(*) INTO total FROM orders;
    SELECT total AS total_orders;
END //

DELIMITER ;

📋 Key differences

  • T-SQL local variables always carry the @ prefix, both at declaration and every use; MySQL's DECLAREd variables have no sigil — they read like plain identifiers, which means naming collisions with column names are a real risk (see the gotcha below).
  • T-SQL assigns a variable from a query with SELECT @Total = COUNT(*) FROM ...; MySQL uses SELECT COUNT(*) INTO total FROM ... — the target comes after INTO, not before an equals sign in the select list.
  • MySQL requires all DECLARE statements to appear at the very top of a BEGIN ... END block, before any other statement; T-SQL is more relaxed about where a DECLARE can appear inside the body.

⚠️ Gotcha

Because MySQL variables have no @-style prefix, naming one the same as a column (e.g. DECLARE customer_id INT; next to a WHERE customer_id = customer_id) silently compares the column to itself instead of to your intended value — MySQL resolves the bare name to the column, not the variable, in that context. The conventional fix is exactly what the earlier example did: prefix procedure parameters with p_ and local variables with something equally distinct, so there's never a name that could resolve to either a column or a variable.

Calling a procedure

SQL Server (T-SQL)
EXEC dbo.GetOrdersByCustomer @CustomerId = 42;
MySQL
CALL get_orders_by_customer(42);

EXEC/EXECUTE becomes CALL. MySQL's positional arguments are far more common than named ones — there's no @CustomerId = 42-style named-parameter calling convention in standard MySQL CALL syntax; arguments are matched to parameters purely by position, so getting the parameter order right in the CREATE PROCEDURE definition matters more than it does in T-SQL, where named arguments let you reorder them at the call site.

🔧 Try It Yourself

Take a simple T-SQL procedure you've written — one parameter, one SELECT, one local variable — and rewrite it for MySQL: wrap it in DELIMITER // / DELIMITER ;, drop the @ prefixes, move the parameter mode (IN) into the parameter list, and rewrite any SELECT @var = ... assignment as SELECT ... INTO var.

🙋 There Are No Dumb Questions

Q: Do I have to change the delimiter back to semicolon after every single procedure I create?
A: Yes, if you want to run any other ordinary statement right afterward in the same session — DELIMITER ; just tells the client "go back to treating semicolon as the statement terminator." It's purely a client-side parsing convenience (the server never sees a "delimiter" concept at all), so different tools handle it differently: some GUI clients let you run an entire procedure body without ever touching DELIMITER, because they already know not to split on every semicolon inside a script.

✅ Quick Check

Why does a MySQL script typically start a stored procedure definition with DELIMITER //?

Next up: time for the recap — but first, the gotchas that don't fit neatly into syntax tables: case sensitivity, transaction engines, and a few functions that quietly behave differently.

← Back to
Next →