sp_executesql Introduction and use (RPM)

Execute believes everyone is using the cooked, abbreviated to exec, in addition to executing stored procedures, is generally used to perform dynamic SQL sp_executesql,sql2005 introduced in the new system stored procedures, but also for the processing of dynamic SQL, such as: Exec sp_ ExecuteSQL @sql, N ' @count int out, @id varchar (a), @cou out, @id @sql as a dynamic SQL N ' @count int out, @id varchar (20) ' for the dynamic SQL to be spelled The parameter list inside @cou out, @id the list of external parameters that provide values for the parameter list in dynamic 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 (a) DECLARE @id varchar Set @id = ' 1 ' Set @sql = ' SELECT count (*) from EMP where id= ' + @id exec @sql I want to get the count (*) out, with the traditional exec is not good, but with sp_executesql it is easy to do: declare @sql nvarchar () Dec Lare @cou int DECLARE @id varchar set @id = ' 1 ' Set @sql = ' Select @count =count (*) from EMP where [email protected] ' EXEC sp_executesql @sql, N ' @count int out, @id varchar ", @cou out, @id print @cou 2. Performance can be seen, if you use Exec, because each incoming @id is different, so each @sq generated L is not the same, so every time you execute a Sql2005 you have to re-compile the dynamic SQL to be executed again but sp_executesql is not the same, because the numeric parameterization, the dynamic SQL to be executed will never change, but the value of the passed parameter is changing, That every time the execution of the second with the recompile, the speed is much faster than the natural, ha!
Note: 1.sp_executesql requires dynamic SQL and dynamic SQL parameter lists to be nvarchar, such as the last example of @sql,n ' @count int out, @id varchar (20) ' I remember a sql2005 in the middle of the Sp3, but I did not hit the patch after the nvarchar 2. The argument list for dynamic SQL must be consistent with the parameter list order of externally supplied values, such as: N ' @count int out, @id varchar ( ", @cou out, @id @count corresponding @cou, @id corresponding @id if not consistent, must be explicitly marked, such as: N ' @count int out, @id varchar (a) ', @id = @id, @[email protected] Out 3. The argument list for dynamic SQL can have the same name as the parameter list parameter name of the externally supplied parameter