Is EXEC necessary for stored procs?

[maccaroo]

Hi

I'm a little confused as to the need to use EXEC when calling a stored procedure. Sometimes I get errors and sometimes I get away with it. Is there a way to tell when it is necessary?

For example, I have a stored proc which runs 3 other stored procs. The 1st and 3rd one only work with EXEC in front, but the 2nd one runs fine without. FYI, the 2nd and 3rd proc do practically the same thing, but to different tables:

--Check for new types
EXEC usp_update_types_table

--Move through contact audits one by one
usp_resolve_single_user_audit

--Move through prop audits one by one
EXEC usp_resolve_single_org_audit

 

[JamesLean]

The only time you can leave the EXEC out is if the SP call is the first line of a batch. Personally, I am in the habit of always using EXEC - that way you don't need to worry about errors and it makes it more obvious, when looking at the script, what you're doing.

This is fine (no EXEC):

apMyProc

This will fail if you remove the EXEC:

SELECT c1 FROM t1

EXEC apMyProc

--James

 

[maccaroo]

Thanks

I'll stick on the EXEC's from now on.