Lesson 4 of 5

Stored Procedures

This is where the two dialects diverge the most. MySQL's procedural extensions and T-SQL'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 good news is that T-SQL actually drops one piece of ceremony MySQL developers are used to fighting: there's no DELIMITER dance at all, because GO (a client-side batch separator, not a real T-SQL keyword) handles the equivalent job differently.

💡 The Bottom Line

T-SQL local variables are always prefixed with @ and declared with DECLARE @name TYPE; right inside the procedure body — no separate parameter-mode ceremony like MySQL's IN/OUT/INOUT tags, because @-prefixed parameters are just declared before AS and used directly. There's also no DELIMITER switch to remember: T-SQL parses a whole CREATE PROCEDURE ... BEGIN ... END statement as one batch, semicolons and all, ending only at an actual GO batch separator (which most GUI tools insert automatically). SQL Server also has a real schema concept (dbo.) that MySQL's flat per-database namespace doesn't.

Basic procedure shape

MySQL (what you know)

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 ;

SQL Server (the new equivalent)

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId INT
AS
BEGIN
    SELECT OrderId, OrderDate
    FROM Orders
    WHERE CustomerId = @CustomerId;
END;

📋 Key differences

MySQL parameters are declared inside the parentheses right after the procedure name, each tagged IN, OUT, or INOUT, with no @ prefix. SQL Server parameters are declared before AS, always prefixed with @, and used the same way inside the body — output parameters are marked with an OUTPUT keyword after the type instead of an IN/OUT tag before it.
MySQL's BEGIN ... END block is effectively mandatory for anything beyond a single statement; SQL Server wraps the body in BEGIN ... END optionally — a single statement doesn't strictly need it, though most style guides recommend always including it for clarity.
MySQL procedures live directly inside a database, with no schema layer; SQL Server almost always prefixes the procedure name with a schema, dbo.GetOrdersByCustomer being the default schema — there's no MySQL equivalent to dbo, since MySQL's databases are already the outermost user-facing namespace.
Naming convention differs by culture more than by rule: MySQL shops overwhelmingly use snake_case; T-SQL shops commonly use PascalCase — neither engine enforces either.

⚠️ Gotcha

Muscle memory says "I need to wrap this in DELIMITER // first" — but T-SQL has no such requirement, and typing MySQL's delimiter syntax into a SQL Server Management Studio query window just throws a syntax error, since DELIMITER isn't a T-SQL keyword at all. What T-SQL does have is GO, a batch separator recognized by client tools (not the SQL Server engine itself) that splits a script into separately-executed batches — you'll typically see one after a CREATE PROCEDURE statement, mostly because CREATE PROCEDURE must be the first statement in its batch, not because of any semicolon-counting problem like MySQL's.

Declaring variables

MySQL (what you know)

DELIMITER //

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

DELIMITER ;

SQL Server (the new equivalent)

CREATE PROCEDURE dbo.CountOrders
AS
BEGIN
    DECLARE @Total INT;
    SELECT @Total = COUNT(*) FROM Orders;
    SELECT @Total AS TotalOrders;
END;

📋 Key differences

MySQL's DECLAREd variables have no sigil — they read like plain identifiers, which risks a naming collision with a column of the same name. T-SQL local variables always carry the @ prefix, both at declaration and every use, which makes them visually unambiguous from column names at every point in the body — the exact class of bug the gotcha below describes for MySQL simply can't happen in T-SQL.
MySQL assigns a variable from a query with SELECT COUNT(*) INTO total FROM ... — the target comes after INTO. T-SQL uses SELECT @Total = COUNT(*) FROM ... instead — the assignment happens in the select list itself, before the FROM, which reads more like a variable assignment statement than a query.
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 — you can declare a variable partway through, right before you first need it.

⚠️ Gotcha

The habit of naming a MySQL local variable the same as a column (protected there only by convention, e.g. a p_ prefix) doesn't create the same risk in T-SQL — but it's easy to overcorrect and assume the @ prefix alone prevents every kind of confusion. It doesn't prevent shadowing between an outer variable and a parameter of the same name, and it doesn't stop SELECT @Total = COUNT(*) FROM Orders WHERE Total > @Total-style typos where you meant a column called Total but there's no such column — that just throws an "invalid column name" error rather than silently comparing the variable to itself the way an unprefixed MySQL name could.

Calling a procedure

MySQL (what you know)

CALL get_orders_by_customer(42);

SQL Server (the new equivalent)

EXEC dbo.GetOrdersByCustomer @CustomerId = 42;
-- or positionally: EXEC dbo.GetOrdersByCustomer 42;

CALL becomes EXEC/EXECUTE. Where MySQL's arguments are matched to parameters purely by position, T-SQL supports named arguments at the call site (@CustomerId = 42), which means you can reorder them, skip optional ones entirely, or make a multi-parameter call self-documenting — a flexibility MySQL's plain positional CALL(...) syntax doesn't offer.

🔧 Try It Yourself

Take a simple MySQL procedure you've written — one parameter, one SELECT, one local variable — and rewrite it for SQL Server: drop the DELIMITER lines entirely, move the parameter above AS with an @ prefix (no IN tag needed), and rewrite any SELECT ... INTO var assignment as SELECT @var = .... Then call it once positionally and once using a named @ParamName = value argument.

🙋 There Are No Dumb Questions

Q: If T-SQL doesn't need a DELIMITER switch, why do scripts I find online still have GO scattered everywhere?
A: GO isn't solving the same problem DELIMITER solves in MySQL — it's a batch separator recognized only by client tools (SSMS, sqlcmd), never sent to the server as SQL at all. Some statements (like CREATE PROCEDURE) must be the only statement in their batch, so a GO before and after is common defensive style, but it's not required just to get the semicolons inside a procedure body to parse correctly — T-SQL never confuses an inner semicolon for the end of the outer CREATE PROCEDURE statement the way an un-delimited MySQL script would.

✅ Quick Check

A MySQL developer, out of habit, tries to type DELIMITER // before a CREATE PROCEDURE statement in SQL Server Management Studio. What happens?

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

← Back to