sp_executesql Introduction and use of Execute I believe everyone uses the cooked, abbreviated to exec, except for the execution of stored procedures, is generally used to perform dynamic SQL
The new system stored procedures introduced in sp_executesql,sql2005 are also used to handle dynamic SQL, such as:
EXEC sp_executesql @sql, N ' @count int out, @id varchar ", @cou out
, @id
@sql to a dynamic SQL for consolidation
N ' @count int out, @id varchar (20) ' As a parameter list in the dynamic SQL to be spelled
@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 (2000)
DECLARE @id varchar (20)
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 (2000)
DECLARE @cou int
DECLARE @id varchar (20)
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
As you can see, if you use Exec, because each incoming @id is not the same, each time the generated @sql is different, so that each time Sql2005 must be executed again to re-compile the dynamic SQL
But sp_executesql is not the same, due to the numerical parameterization, the dynamic SQL to be executed 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!
Attention:
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 the varchar in sql2005, But after I hit the Sp3 patch, I can't, I have to nvarchar.
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. Dynamic SQL parameter list with external parameter list parameter name can have the same name as the original: https://www.cnblogs.com/wanyuan8/archive/2011/11/09/2243483.html
The difference between EXEC and sp_executesql in SQL Server
