sp_executesql Introduction and use

Execute believes everyone is using the familiar, shorthand forexec, in addition to executing stored procedures, is typically used to perform dynamicSql
sp_executesql,The new system stored procedures introduced in sql2005 are also used to handle dynamicof SQLSuch as:
EXEC sp_executesql @sql, N ' @count int out, @id varchar ", @cou out
, @id
@sql for the dynamic of the spellSql
N ' @count int out, @id varchar (20) ' for the dynamic of the spellList of parameters in SQL
@cou out, @id for the dynamicAn external parameter list that provides values for the parameter list in SQL

So what's the difference between them?

1, the biggest difference between them is the embedded parameters, such as the following statement
declare @sql nvarchar (2000)
DECLARE @id varchar (20)
Set @id = ' 1 '
Set @sql = ' SELECT count (*) from EMP where id= ' + @id
EXEC @sql
I want to get theCOUNT (*) came out, with the traditionalexec is not good, but withsp_executesql is easy to do:
declare @sql nvarchar ($)
Declare @cou int
declare @id varchar ()
Set @id = ' 1 '
S ET @sql = ' Select @count =count (*) from EMP where [email protected] '
exec sp_executesql @sql, N ' @count int out, @id V Archar ', @cou out
, @id
Print @cou
2. Performance
you can see that if you use exec, because each incoming @id different, so each generated @sql is not the same, so that every time sql2005 will have to be executed again the dynamic Span lang= "en-us" >sql recompile once
but sp_executesql is not the same, due to the numerical parameterization, the dynamic sql will never change, but the value of the parameters passed in the change, that every time the execution of the second with a recompile, the speed naturally much faster ha!

Note:
1.sp_executesql requires dynamic The SQL and dynamic sql parameter list must be nvarchar, such as the

2. The parameter list of dynamic SQL must match the order of the parameter list of the externally supplied values, such as:
N ' @count int out, @id varchar ", @cou out, @id
@count corresponding @cou, @id corresponding @id
If they are not consistent, they must be explicitly marked, such as:
N ' @count int out, @id varchar ", @id =@id, @[email protected] out
3. The parameter list fordynamic SQL can have the same name as the parameter list parameter name of the externally supplied parameter

sp_executesql Introduction and use