MSSQL Server EXEC and sp_executesql

MSSQL Server EXEC and sp_executesql

, Exec's use

The use of 2,sp_executesql

MSSQL provides us with two commands for dynamically executing SQL statements, namely, EXEC and

Sp_executesql Usually, sp_executesql is more advantageous, it provides the input and output interface, while Exec does not

Yes. One of the biggest benefits of using sp_executesql is the ability to reuse the execution plan, which provides

Perform performance (as I'll explain in a later example), you can also write more secure code. Exec

In some cases it will be more flexible. Unless you have compelling reasons to use Exec, no side try to use

sp_executesql.

The use of 1,exec

The EXEC command has two uses, one is to execute a stored procedure, the other is to perform a dynamic batch. To

The second use of the following is said.

Let's use exec to demonstrate an example, code 1

DECLARE @TableName VARCHAR (m), @Sql NVARCHAR (MAX), @OrderID INT; SET

@TableName = ' Orders '; SET @OrderID = 10251; SET @sql = ' SELECT * FROM

' +quotename (@TableName) + ' WHERE OrderID = ' +cast (@OrderID as VARCHAR (10)) + '

ORDER by ORDERID DESC ' exec (@sql); Note: Only one string variable is allowed in the EXEC brackets here

Quantity, but can concatenate multiple variables if we write this exec:

EXEC (' SELECT top (' + CAST (@TopCount as VARCHAR) + ') * +quotename

(@TableName) + ' ORDER by ORDERID DESC ');
The SQL compiler will have an error, the compilation does not pass, and if we do:
EXEC (@sql + @sql2 + @sql3), the compiler will pass;

So the best thing to do is to construct the code into a variable and then use that variable as an input parameter to the EXEC command

, so that it will not be restricted;

exec does not provide an interface

The interface here means that it cannot perform a batch with a variable character, which at first glance doesn't seem to understand

, it does not matter, I have an example below, you know what the meaning of a look.
DECLARE @TableName VARCHAR (m), @Sql NVARCHAR (MAX), @OrderID INT; SET

@TableName = ' Orders '; SET @OrderID = 10251; SET @sql = ' SELECT * FROM

' +quotename (@TableName) + ' WHERE OrderID = @OrderID ORDER by OrderID

DESC ' EXEC (@sql); in the word set @sql, if we run this batch, the compiler

will produce a mistake.

MSG 137, level, State 2, line 1
The scalar variable "@OrderID" must be declared.

When using exec, if you want to access a variable, you must concatenate the contents of the variable into a dynamically constructed code string, such as

: SET @sql = ' SELECT * from ' +quotename (@TableName) + ' WHERE OrderID =

' +cast (@OrderID as VARCHAR) + ' ORDER by OrderID DESC '

The contents of the concatenated variables also have performance drawbacks. SQL Server creates a new hold for each query string

Row schedules, even if the query pattern is the same. To demonstrate this, first empty the execution plan in the cache

DBCC Freeproccache (This is not what this article covers, you can view Ms MSDN)

Http://msdn.microsoft.com/zh-cn/library/ms174283.aspx

Run the Code 1 3 times, assigning the following 3 values to @orderid respectively, 10251,10252,10253. and then use

The following code queries

SELECT cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects WHERE sql