Sp_executesql introduction and usage

Execute I believe everyone is familiar with it. It is abbreviated as exec. In addition to executing stored procedures, it is generally used to execute dynamic SQL statements.

New system stored procedures introduced in sp_executesql and sql2005 are also used to process dynamic SQL statements, such:

Exec sp_executesql @ SQL, n' @ count int out, @ id varchar (20) ', @ cou out

, @ Id

@ SQL: a dynamic SQL statement.

N' @ count int out, @ id varchar (20) 'indicates the list of parameters in the assembled dynamic SQL statement.

@ Cou out, @ id is the external parameter list that provides values for the parameter list in dynamic SQL.

So what are the differences between them?

1. The biggest difference between them is embedded parameters, as shown in 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 pass the obtained count (*). It is difficult to use traditional exec, but it is easy to use sp_executesql:

Declare @ SQL nvarchar (2000)

Declare @ cou int

Declare @ id varchar (20)

Set @ id = '1'

Set @ SQL = 'select @ count = count (*) from emp where id = @ id'

Exec sp_executesql @ SQL, n' @ count int out, @ id varchar (20) ', @ cou out

, @ Id

Print @ cou

2. Performance

As you can see, if exec is used, because the @ id passed in each time is different, the @ SQL generated each time is different. In this way, each time Sql2005 is executed, the dynamic SQL to be executed must be re-compiled once.

However, sp_executesql is different. Because the value is parameterized, the dynamic SQL to be executed will never change, but the value of the input parameter will change. Then, re-compile the SQL statement in seconds during each execution, the speed is naturally much faster!

Note:

1. sp_executesql requires that the dynamic SQL and dynamic SQL parameter lists must be Nvarchar. For example, in the previous example, @ SQL, n' @ count int out, @ id varchar (20) 'I remember that Varchar can be used in sql2005, but it won't work after I patch Sp3. It must be Nvarchar.

2. The parameter list of dynamic SQL must be consistent with that of the parameter list of external values, for example:

N' @ count int out, @ id varchar (20) ', @ cou out, @ id

@ Count corresponds to @ cou, @ id corresponds to @ id

If they are inconsistent, they must be explicitly marked, for example:

N' @ count int out, @ id varchar (20) ', @ id = @ id, @ count = @ cou out

3. The parameter list of dynamic SQL can have the same name as the parameter list of external parameters.