SQL SERVER – Difference Between EXEC and EXECUTE vs EXEC() – Use EXEC/EXECUTE for SP always

What is the difference between EXEC and EXECUTE?

They are the same. Both of them executes stored procedure when called as
EXEC sp_help
GO
EXECUTE sp_help
GO

I have seen enough times developer getting confused between EXEC and EXEC(). EXEC command executes stored procedure where as EXEC() function takes dynamic string as input and executes them.
EXEC('EXEC sp_help')
GO

Another common mistakes I have seen is not using EXEC before stored procedure. It is always good practice to use EXEC before stored procedure name even though SQL Server assumes any command as stored procedure when it does not recognize the first statement. Developer learns while working with Query Editor in SSMS that EXEC is not necessary before running any stored procedure. However, consider following two test and you will see why EXEC or EXECUTE is necessary in many cases and good practice to use it.

TEST 1 : No Errors
USE AdventureWorks;
GO
----Try this first independently this will throw an error
sp_helptext 'dbo.uspPrintError'
GO
----Try this second independently this will work fine
EXEC sp_helptext 'dbo.uspPrintError'
GO

TEST 2 : EXEC prevents error
USE AdventureWorks;
GO
----Try this first independently this will throw an error
SELECT *
FROM Sales.Individual
sp_helptext 'dbo.uspPrintError'
GO
----Try this second independently this will work fine
SELECT *
FROM Sales.Individual
EXEC sp_helptext 'dbo.uspPrintError'
GO

Test 2 indicates that using EXEC or EXECUTE is good practice as it always executes the stored procedure, when not using EXEC can confuse SQL SERVER to misinterpret commands and may create errors.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQLAuthority News – Scrum: Agile Software Development for Project Management
Next Post
SQLAuthority News – Random Article from SQLAuthority Blog

Related Posts

72 Comments. Leave new

  • Rathin Pandya
    May 12, 2014 7:03 pm

    I am using entity framework , with same page somewhere i am using SP, SP takes too much time compare to Entity framework.
    so my question is that , is entity framework better than Stored procedure ? if no then why i face this type of issue ?

    Reply
    • AlwaysLearning
      May 13, 2014 6:27 am

      I’d start by using SQL Profiler to see what SQL is being submitted by Entity Framework to the server and what SET OPTIONS it is using.

      A couple of possibilities:

      (a) EF may be using a different configuration of SET OPTIONS than the stored procedure is internally. SET OPTIONS will change the query planner and caching behaviour and every combination of SET OPTIONS caches its own query plan, even if the SQL code was byte-for-byte identical.

      (b) Maybe the stored procedure is just dumb. Using the exact same SET OPTIONS used by EF in (a) inspect the Actual Execution Plan of your stored procedure and see where the highest costs are. Perhaps your joins are doing too much work? Perhaps you are missing one or more indexes?

      (c) EF will only be selecting the columns that it needs whereas the stored procedure will always select all of the columns that it is instructed to. Computed columns implemented by User-Defined Functions can be very expensive. [Google for discussions on RBAR… Row By Agonizing Row.] Consider replacing the stored procedure with a view as query planner will automatically exclude columns and joins not required for the set of columns being selected from a view.

      Reply
  • This my question
    In Stored procedure we exec spname
    but for me I want
    create one procedure
    while I execute all we use exec SPName
    but I need EXEC(select * from tablename)

    Reply
  • Alireza Nikoughadamazad
    November 17, 2020 10:03 am

    Thanks for such a valuable Tutorial

    Reply

Leave a Reply