Explain the correct way for SQL Server to execute related dynamic SQL

This article mainly introduces the correct way for SQL Server to execute dynamic SQL. Friends who need it can refer to it

If SQL Server executes dynamic SQL, how should it be implemented? The following will introduce you to SQL Server execution dynamics. Two correct ways of SQL, I hope it can give you a deeper understanding of SQL Server executing dynamic SQL

Dynamic SQL: code that is executed dynamically. It is generally a SQL statement block that is dynamically combined based on user input or external conditions. .Dynamic SQL can flexibly exert the powerful functions of SQL and conveniently solve some problems that are difficult to solve by other methods. I believe that anyone who has used dynamic SQL can appreciate the convenience it brings. However, dynamic SQL sometimes suffers from execution performance (efficiency) )The above is not as good as static SQL, and if used improperly, there are often hidden dangers in security (SQL injection attacks).

Dynamic SQL can be executed in two ways: EXECUTE or SP_EXECUTESQL.

　EXECUTE

Execute the command string or string in the Transact-SQL batch or execute one of the following modules: system stored procedure, user-defined stored procedure, standard A value user-defined function or extended stored procedure. SQL Server 2005 extended the EXECUTE statement so that it can be used to send pass commands to a linked server. In addition, you can also explicitly set the context in which a string or command is executed

SP_EXECUTESQL

Execute a Transact-SQL statement or batch that can be reused multiple times or dynamically generated. Transact-SQL statements or batches can contain embedded parameters. In batches, name scopes SP_EXECUTESQL behaves the same as EXECUTE in terms of database context. The Transact-SQL statement or batch in the SP_EXECUTESQL stmt parameter is not compiled until the SP_EXECUTESQL statement is executed. Subsequently, the contents of the stmt are compiled and run as an execution plan. The The execution plan is independent of the execution plan of the batch named SP_EXECUTESQL. The SP_EXECUTESQL batch cannot reference variables declared in the batch that calls SP_EXECUTESQL. Local cursors or variables in the SP_EXECUTESQL batch are not visible to the batch that calls SP_EXECUTESQL. Changes made to the database context are only effective until the end of the SP_EXECUTESQL statement.

If only the parameter values ​​in the statement are changed, sp_executesql can be used to execute the Transact-SQL statement multiple times instead of the stored procedure. Because the Transact-SQL statement itself Remains unchanged, only the parameter values ​​change, so the SQL Server query optimizer may reuse the execution plan generated during the first execution.

Generally speaking, we recommend and give priority to using SP_EXECUTESQL to execute dynamic SQL. On the one hand, it is more flexible and can have input and output parameters. On the other hand, the query optimizer is more likely to reuse execution plans and improve execution efficiency. In addition, using SP_EXECUTESQL can improve security; of course, this does not mean that EXECUTE should be completely abandoned. In certain situations, EXECUTE is more convenient than SP_EXECUTESQL. For example, dynamic SQL strings are of VARCHAR type, not NVARCHAR type. SP_EXECUTESQL can only execute Unicode strings or constants or variables that can be implicitly converted to ntext, while EXECUTE can only execute both. All types of strings can be executed.

Let’s compare some details of EXECUTE and SP_EXECUTESQL.

EXECUTE(N'SELECT * FROM Groups' ) --Execution successful

EXECUTE('SELECT * FROM Groups') --Execution successful

SP_EXECUTESQL N'SELECT * FROM Groups'; --Execution successful

SP_EXECUTESQL 'SELECT * FROM Groups' --Execution error

Summary: EXECUTE can execute non-Unicode or Unicode type string constants and variables. SP_EXECUTESQL can only execute Unicode or string constants that can be implicitly converted to ntext , variable.

DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';

EXECUTE('SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''''); --There is a syntax error near 'SUBSTRING'.

DECLARE @Sql VARCHAR(200);

DECLARE @GroupName VARCHAR(50);SET @GroupName ='SuperAdmin';

　SET@Sql='SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''''

　- -PRINT @Sql;EXECUTE(@Sql);

Summary: EXECUTE The brackets can only contain string variables, string constants, or their combinations, and cannot call other functions, stored procedures, etc. If To use, use a variable combination, as shown above.

DECLARE @Sql VARCHAR(200);

DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';

　SET@Sql='SELECT * FROM Groups WHEREGroupName=@GroupName'

　--PRINT @Sql;EXECUTE(@Sql); --Error: Must declare scalar variable "@GroupName".SET @Sql='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''')

EXECUTE(@Sql); --Correct:

DECLARE @Sql NVARCHAR( 200);

DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';

SET@Sql='SELECT * FROM Groups WHEREGroupName=@GroupName'

PRINT @Sql;

EXEC SP_EXECUTESQL @Sql,N'@GroupNameNVARCHAR',@GroupName

There is no result in the query, and the parameter length is not declared.

DECLARE @Sql NVARCHAR(200);

DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';

　SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName'

　PRINT @Sql;

　EXEC SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)' ,@GroupName

Summary: Dynamic batch processing cannot access local variables defined in the batch. SP_EXECUTESQL can have input and output parameters, which is more flexible than EXECUTE.

Let’s take a look at EXECUTE, SP_EXECUTESQL For execution efficiency, first clear the cache execution plan, then change the @GroupName value SuperAdmin, CommonUser, and CommonAdmin and execute it three times respectively. Then look at the cache information

　DBCC FREEPROCCACHE;

DECLARE @Sql VARCHAR(200);

DECLARE @GroupName VARCHAR(50); SET@GroupName ='SuperAdmin'; --'CommonUser', 'CommonAdmin'

SET@Sql ='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''')

EXECUTE(@Sql); SELECTcacheobjtype, objtype, usecounts, sql

FROM sys.syscacheobjects

　WHERE sql NOTLIKE '%cache%'

　ANDsql NOTLIKE '%sys.%';

Following the same pattern, let’s take a look at the execution efficiency of SP_EXECUTESQL

DBCC FREEPROCCACHE;

DECLARE @Sql NVARCHAR(200);

DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin'; --'CommonUser', 'CommonAdmin'

　SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName'

　EXECUTESP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName;

　SELECTcacheobjtype , objtype, usecounts, sql

FROM sys.syscacheobjects

WHERE sql NOTLIKE '%cache%'

ANDsql NOTLIKE '%sys.%';

Summary: EXEC generated three independent ad hoc execution plans, while SP_EXECUTESQL only generated one execution plan and reused it three times. Imagine if there are many similar dynamic SQLs in a library and they are executed frequently. If you use SP_EXECUTESQL can improve performance.

The following are additions from other netizens

For some special reasons, we need to dynamically create SQL statements in SQL statements or stored procedures, and then Dynamically execute SQL statements or stored procedures.

Here, Microsoft provides two methods, one is to use the

Execute function

The execution method is
Execute(@sql) Dynamically execute a SQL statement, but the SQL statement here cannot get the return result. Let's introduce another method

Use stored proceduressp_ExecuteSql

Use the storage Procedure, you can return the parameters in the dynamic statement.

For example

declare @sql nvarchar(800),@dd varchar(20)
set @sql='set @mm=''测试字符串'''
exec sp_executesql @sql,N'@mm varchar(20) output',@dd output
select @dd

When executed, the value of a variable in the internally created SQL statement will be returned to the external caller.

Mainly comes from an accidental need at work:

create proc proc_InToServer @收费站点编号 varchar(4),@车道号 tinyint,@进入时间 varchar(23),@UID char(16),
@车牌 varchar(12),@车型 char(1),@识别车牌号 varchar(12),@识别车型 char(1),@收费金额 money,@交易状态 char(1),
@有图像 bit,@离开时间 varchar(23),@速度 float,@HasInsert int output
as
begin
  declare @inTime datetime,@TableName varchar(255),@leaveTime datetime,@HasTable bit,@Sql nvarchar(4000)
 select @intime=Convert(datetime,@进入时间),@leaveTime=Convert(datetime,@离开时间)
 set @TableName='ETC03_01_OBE原始过车记录表_'+dbo.formatDatetime(@intime,'YYYYMMDD')

 select @HasTable=(Case when Count(*)>0 then 1 else 0 end) from sysobjects where id=Object_id(@TableName) and ObjectProperty(id,'IsUserTable')=1
 if @HasTable=0
 begin
  set @Sql='CREATE TABLE [dbo].['+@TableName+'] (
 [收费站点编号] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [车道号] [tinyint] NOT NULL,
 [进入时间] [datetime] NOT NULL,
 [UID] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [车牌] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
 [车型] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [识别车牌号] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
 [识别车型] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [收费金额] [money] NULL ,
 [交易状态] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [有图像] [bit] NOT NULL ,
 [离开时间] [datetime] NULL ,
 [速度] [float] NULL,
    Constraint'+' PK_'+@TableName+' primary key(收费站点编号,车道号,进入时间,UID)
    ) ON [PRIMARY]'
   Execute(@Sql)
  end 
  set @sql = 'select @Cnt=count(*) from '+@TableName+ ' where 收费站点编号='''+@收费站点编号+''' and 车道号='+cast(@车道号 as varchar(4))+' and 进入时间='''+@进入时间+''' and UID='''+@UID+''''
  set @sql = @sql + ' if @Cnt=0 '
  
  set @sql=@sql+'insert '+@TableName+' values('''+@收费站点编号+''','+cast(@车道号 as varchar(4))+','''+@进入时间+''','''+@Uid+''','''+@车牌+  
  ''','''+@车型+''','''+  @识别车牌号+''','''+@识别车型+''','+Cast(@收费金额 as varchar(8))+','''+@交易状态+''','+cast(@有图像 as varchar(1))+
  ','''+@离开时间+''','+Cast(@速度 as varchar(8))+')'
  --Execute(@sql) 
  exec sp_executesql @sql,N'@Cnt int output',@HasInsert output
end

Supplementary information 2.

SQL Server executes dynamic SQL statements in a loop.

The query was executed successfully using the Navicate tool.

declare @name nvarchar(100)

declare @sql nvarchar(200)

declare @i int
set @i =10000

while @i<=99999
begin
	set @name = &#39;test&#39; + cast(@i as varchar(20))
	set @sql =N&#39;SELECT * INTO &#39;+ @name +&#39; FROM test&#39;
	exec sp_executesql @sql
	print @name

 set @i=@i + 1
end

The above is the detailed content of Explain the correct way for SQL Server to execute related dynamic SQL. For more information, please follow other related articles on the PHP Chinese website!