What is the difference between CALL and EXEC in T-SQL?

Question

Consider:

CREATE PROCEDURE LowerCityDiscounts @city VARCHAR(45), @decrease DECIMAL(10,2) AS
BEGIN
    BEGIN TRANSACTION;
    UPDATE Customers SET discnt = discnt - @decrease
    WHERE Customers.city = @city;

    UPDATE Customers SET discnt = 0
    WHERE Customers.city = @city AND discnt < 0
    COMMIT;
END;

I tried to call this procedure with:

CALL LowerCityDiscounts 'Cleveland', 5;

but this only produces

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Cleveland'.

Yet, if I change things to

EXEC LowerCityDiscounts 'Cleveland', 5;

everything works fine. This despite that the documentation stating that call is the right syntax.

Why does EXEC work when CALL does not?

Answer 1

Yup.. CALL is an construct/syntax usable from an ODBC driver, as your documentation indicates.

There's no reference in the T-SQL documentation to CALL, only EXEC.

It doesn't work because it's not T-SQL.

Answer 2

The T-SQL language does not recognise ODBC escape sequences; EXEC is the only command available for calling a stored procedure. ODBC escape sequences are interpreted by client-side libraries (e.g. ODBC, OLE DB, ADO, ADO.NET) and translated to real T-SQL syntax on the fly before execution.

The end result is, you can call your top-level stored procedure from the client using CALL if you want to, but if that procedure calls others, it must use EXEC.

The same principle applies for the date/time literal escape sequences.