What is the difference between EXEC and sp_executesql ?

kishore_peddi

Hi,

Please let me know what is the difference between EXEC command and sp_executesql ?

Thanks,
Kishore

diegonzab

:
: Hi,
:
: Please let me know what is the difference between EXEC command and sp_executesql ?
:
: Thanks,
: Kishore
:

The main difference is that sp_executesql allows you to include parameters in your query without the need to make any conversion:

DECLARE @datevar datetime
...
EXEC sp_executesql N'select * from T1 where T1.date1= @datevar', N'@datevar datetime', @datevar
...
this will let you run your query with parameters.

Second, if you're planning to run a query, (or very similar queries)several times, then it's more efficient to run it with sp_executesql, since the EXEC will recompile the query every time, while sp_executesql will, very probably, use the same execution plan for each execution