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 = 'test' + cast(@i as varchar(20)) set @sql =N'SELECT * INTO '+ @name +' FROM test' 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!