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?

Solution 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.

Solution 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.

Solution 3

I ran across an issue (while migrating databases) that MSSQL will accept CALL statement in a stored procedure - the SQL Management Studio complains but the query itself is executed successfully.

So a statement like this does execute:

create procedure spwho
as begin
    call sp_who2
end
go

exec spwho

Unfortunately even though the procedure is created, it does not produce any results (but neither does it produce any errors or warnings).

So in cases like this the CALL statement will not produce errors in MSSQL but anyway should never used since it does not work.

Comments

[Martin Smith]

{ CALL [Foo] } gets rewritten by the ODBC driver as EXEC before SQL Server sees it. The same doesn't seem to be true for the date/time literal escape sequences though. SQL Server does natively understand them as far as I could determine.