Differences between EXEC and SP_EXECUTESQL

Today I did some research on the 2 commands of MS SQL 2005 and here is result.

I was looking at the differences between EXEC and SP_EXECUTESQL.

Both these commands are used to run procedures or dynamic queries.

Lets have an example. Suppose we have the following stored procedure:

Create procedure Sample_UsingDynamicQueries
@table varchar(max)
As
Declare @strQuery nvarchar(4000)
Select @strQuery = 'select * from dbo.' + quotename(@table)
exec sp_executesql @strQuery -------- (A)
--exec (@strQuery) ---------------------- (B)

Execute dbo.samplesp_usingdynamicqueries 'EmpDetails'

Now, after running this stored procedure whether we use the line which is marked as (A) or (B) it would give us the same result.

The difference between both is that Exec statement is Unparameterised whereas sp_executeSql is Parameterised.

For example while using execute(), if we write a query which takes a parameter lets say "EmpID". When we run the query with "EmpID" as 1 and 2 it would be creating two different cache entries (one each for value 1 and 2 respectively).

On the contrary, if we use sp_executeSql, the cached plan would be created only once and would be reused 'n' number of times for ‘n’ number of parameters.

So this would have better performance.

For example:

Select @strQuery = 'Select E.EmpName from dbo.empdetails E where E.EmpID = N''1'''
Exec (@strQuery)

Select @strQuery = 'Select E.EmpName from dbo.empdetails E where E.EmpID = N''2'''
Exec (@strQuery)

Select @strQuery = 'Select E.EmpName from dbo.empdetails E where E.EmpID = @EmpID'
Exec sp_executesql @strQuery, N'@EmpID int', 1
Exec sp_executesql @strQuery, N'@EmpID int', 2

Now using Exec, 2 separate execution plans will be created.

But when we are using sp_executesql, the execution plan will be created only once and will be reused for the 2 parameters and hence the time would be saved in this.