STORED PROCS: WHEN TO USE EXECUTE COMMAND?

While running stored procedures in SQL Server, you may have noticed that it seems to make no difference whether or not you use the EXECUTE (or EXEC) command before the procedure name. When should you use EXEC – if at all? The Books Online article for Executing Stored Procedures says that EXEC is only needed if more than one stored procedure is run in a batch. This means I can execute one sproc call with or without EXEC:

USE AdventureWorks
GO
sp_helpfile

or:

USE AdventureWorks
GO
EXEC sp_helpfile

But if I need to run more than one sproc at the same time (in a batch), I have to use:

USE AdventureWorks
GO
EXEC sp_spaceused ‘Person.Contact’, true
EXEC sp_helpfile
GO

or just:

EXEC sp_spaceused ‘Person.Contact’, true
EXEC sp_helpfile

Both statements return the dual result set below:

http://msdn.microsoft.com/en-us/library/ms189915.aspx