Share the correct way for SQL Server to execute dynamic SQL, serversql

Share the correct way for SQL Server to execute dynamic SQL, serversql

What should I do if SQL Server executes dynamic SQL statements? The following describes the two correct ways for SQL Server to execute dynamic SQL statements. We hope that you can have a better understanding of SQL Server to execute dynamic SQL statements.

Dynamic SQL: code that is executed dynamically. it is generally an SQL statement block dynamically combined based on user input or external conditions. dynamic SQL can give full play to the powerful functions of SQL and easily solve problems that are difficult to solve by other methods. I believe that anyone who has used dynamic SQL can realize the convenience it brings. However, dynamic SQL is sometimes not as good as static SQL in execution performance (efficiency) and is not suitable for use, there are often security risks (SQL injection attacks ).

Dynamic SQL can be executed through EXECUTE or SP_EXECUTESQL.

  EXECUTE

Execute a command string or string in a Transact-SQL batch or execute one of the following modules: system stored procedures, user-defined stored procedures, scalar value user-defined functions, or extended stored procedures. SQL Server 2005 extends the EXECUTE statement to send commands to the linked Server. in addition, you can explicitly set the context for executing strings or commands.

  SP_EXECUTESQL

Execute a statement or batch process that can be repeatedly used or dynamically generated multiple times. transact-SQL statements or batch processing can contain embedded parameters. SP_EXECUTESQL and EXECUTE behave the same in batch processing, name scope, and database context. the Transact-SQL statement or batch processing in the SP_EXECUTESQL stmt parameter is compiled only when the SP_EXECUTESQL statement is executed. then, the content in stmt will be compiled and run as an execution plan. this execution plan is independent of the Execution Plan for batch processing called SP_EXECUTESQL. SP_EXECUTESQL batch processing cannot reference the variable declared in the batch processing that calls SP_EXECUTESQL. the local cursor or variable in SP_EXECUTESQL batch processing is invisible to the batch processing that calls SP_EXECUTESQL. changes made to the database context are valid only before the end of the SP_EXECUTESQL statement.

If only the parameter values in the statement are modified, sp_executesql can be used to execute the Transact-SQL statement multiple times instead of the stored procedure. because the Transact-SQL statement remains unchanged and only the parameter value changes, the SQL Server Query Optimizer may reuse the execution plan generated during the first execution.

In general, we recommend that you use SP_EXECUTESQL to execute dynamic SQL statements. 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 the execution plan, improve Execution efficiency. in addition, the use of SP_EXECUTESQL can improve security. Of course, it does not mean that EXECUTE should be abandoned completely. In specific cases, EXECUTE is more convenient than SP_EXECUTESQL. For example, dynamic SQL strings are of the VARCHAR type and are not of the NVARCHAR type. SP_EXECUTESQL can only EXECUTE Unicode strings or constants or variables that can be implicitly converted to ntext, while EXECUTE can EXECUTE both types of strings.

Next we will 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 string constants and variables. While SP_EXECUTESQL can only EXECUTE Unicode or string constants and variables that can be implicitly converted to ntext.

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

EXECUTE ('select * FROM Groups WHERE GroupName = ''' + SUBSTRING (@ GroupName,) + '''); -- there is a syntax error near 'string.

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 brackets can only contain string variables, string constants, or their connection combinations. Other functions and stored procedures cannot be called. if you want to use it, use a combination of variables, as shown in the preceding figure.

DECLARE @ SQL VARCHAR (200 );

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

SET @ SQL = 'select * FROM Groups WHEREGroupName = @ GroupName'

-- PRINT @ SQL; EXECUTE (@ SQL); -- error: the scalar variable "@ GroupName" must be declared ". 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

No results are found, and no parameter length is 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 the local variable defined in the batch processing. SP_EXECUTESQL can have input and output parameters, which is more flexible than EXECUTE.

Next, let's take a look at the execution efficiency of EXECUTE and SP_EXECUTESQL. First, clear the cache execution plan, and then use the @ GroupName value SuperAdmin, CommonUser, and CommonAdmin to EXECUTE the plan three times. Then, let's take a look at the cached 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. % ';

Let's 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 generates three independent ad hoc execution plans, while SP_EXECUTESQL is used to generate only one execution plan, which is reused three times, there are many such dynamic SQL statements that are frequently executed. Using SP_EXECUTESQL can improve the performance.

The following is a supplement from other netizens:

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

Here, Microsoft provides two methods:

Execute function

The execution method is
Execute (@ SQL) to dynamically Execute an SQL statement, but the SQL statement here cannot get the returned results. Next we will introduce another method.

Use stored proceduresSp_ExecuteSql

You can use this stored procedure to return parameters in dynamic statements.

For example

declare @sql nvarchar (800), @dd varchar (20)
set @ sql = 'set @ mm = `` Test string' ''
exec sp_executesql @ sql, N '@ mm varchar (20) output', @ dd output
select @dd

Execute the SQL statement to return the value of a variable created internally to an external caller.

It mainly comes from an accidental need at work:

create proc proc_InToServer @ toll site number varchar (4), @ 车道 号 tinyint, @ 入 时间 varchar (23), @UID char (16),
@ 车牌 varchar (12), @ Model char (1), @ Identify the license plate number varchar (12), @ identify the model char (1), @ Charge money, @ Transaction status char (1),
@ 有 graphic bit, @ 走 时间 varchar (23), @speed float, @HasInsert int output
as
begin
  declare @inTime datetime, @ TableName varchar (255), @ leaveTime datetime, @ HasTable bit, @ Sql nvarchar (4000)
 select @ intime = Convert (datetime, @ Enter time), @ leaveTime = Convert (datetime, @ Leave time)
 set @ TableName = 'ETC03_01_OBE original passing record table _' + 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 + '] (
 [Charge Site Number] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [Lane number] [tinyint] NOT NULL,
 [Entry time] [datetime] NOT NULL,
 [UID] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [License plate] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL,
 [Model] [char] (1) COLLATE Chinese_PRC_CI_AS NULL,
 [Identify license plate number] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL,
 [Identify vehicle model] [char] (1) COLLATE Chinese_PRC_CI_AS NULL,
 [Charge] [money] NULL,
 [Transaction Status] [char] (1) COLLATE Chinese_PRC_CI_AS NULL,
 [There is an image] [bit] NOT NULL,
 [Leave time] [datetime] NULL,
 [Speed] [float] NULL,
    Constraint '+' PK _ '+ @ TableName +' primary key (toll station number, lane number, entry time, UID)
    ) ON [PRIMARY] '
   Execute (@Sql)
  end
  set @sql = 'select @ Cnt = count (*) from' + @ TableName + 'where toll station number =' '' + @ toll station number + '' 'and lane number =' + cast (@ 车道 号 as varchar ( 4)) + 'and entry time =' '' + @ entry time + '' 'and UID = ``' + @ UID + '' ''
  set @sql = @sql + 'if @ Cnt = 0'
  
  set @ sql = @ sql + 'insert' + @ TableName + 'values (' '' + @ toll station number + '' ',' + cast (@ 车道 号 as varchar (4)) + ',' '' + @ Enter Time + '' ',' '' + @ Uid + '' ',' '' + @ 车牌 +
  '' ',' '' + @ Model + '' ',' '' + @Identify the license plate number + '' ',' '' + @ Identify the model + '' ',' + Cast (@charge amount as varchar ( 8)) + ',' '' + @ dealing status + '' ',' + cast (@ 有 图片 as varchar (1)) +
  ',' '' + @ Leave time + '' ',' + Cast (@speed 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 cyclically.

The query is successfully executed 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 correct way for SQL Server to execute dynamic SQL statements compiled for you by the help house editor, hoping to help you.