SQL Server executes dynamic SQL in the right way

How should SQL Server implement dynamic SQL? Here's how SQL Server performs dynamic SQL two of the right way, and hopefully gives you a deeper understanding of SQL Server's dynamic SQL execution.

Dynamic Sql:code This is executed dynamically. It is generally a block of SQL statements that are dynamically combined based on user input or external conditions. Dynamic SQL can be flexible to play a powerful function of SQL, It is easy to solve some problems which are difficult to solve by other methods. It is believed that people who have used dynamic SQL can appreciate the convenience, but dynamic SQL is sometimes less efficient than static SQL, and is not properly used, and is often a security risk (SQL injection attack).

Dynamic SQL can be executed either by execute or sp_executesql.

EXECUTE

Executes a command string, string, or one of the following modules in a Transact-SQL batch: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure. SQL Server 2005 extends the EXECUTE statement so that it can be used to send delivery commands to the linked server. In addition, you can explicitly set the context of an execution string or command

sp_executesql

Execute Transact-SQL statements or batches that can be reused or dynamically generated multiple times. Transact-SQL statements or batches can contain embedded parameters. In the batch, name scope, and database context, sp_executesql behaves the same as EXECUTE. Transact-SQL statements or batches in the sp_executesql stmt parameter are compiled when the sp_executesql statement is executed. The contents of the stmt are then compiled and run as execution plans. The execution plan is independent of the name sp_ ExecuteSQL the execution plan for the batch process. A sp_executesql batch cannot reference a variable declared in a batch that calls sp_executesql. A local cursor or variable in a sp_executesql batch is not visible to a batch that invokes sp_executesql. Changes made to the database context are valid only until the end of the sp_executesql statement.

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

In general, we recommend and prioritize the use of sp_executesql to execute dynamic SQL, on the one hand, it is more flexible, can have input and output parameters, on the other hand, the query optimizer is more likely to reuse the execution plan, improve execution efficiency. and use SP_. ExecuteSQL can improve security, and of course it's not that you want to completely discard execute, and in a particular situation, execute is more convenient than sp_executesql, such as a dynamic SQL string that is a varchar type, not a nvarchar type. sp_ ExecuteSQL can only perform strings that are Unicode or constants or variables that can be implicitly converted to ntext, and execute has two types of strings.

Let's take a look at some of the details of execute and sp_executesql.

Execute (N ' SELECT * from Groups ')--Successful execution

Execute (' SELECT * from Groups ')--Successful execution

sp_executesql N ' SELECT * from Groups '; --Successful execution

sp_executesql ' SELECT * from Groups '--Error executing

Summary:execute can perform string constants, variables that are not Unicode or Unicode types. sp_executesql can only perform Unicode or string constants, variables that can be implicitly converted to ntext.

DECLARE @GroupName VARCHAR (50); Set@groupname = ' superadmin ';

EXECUTE (' SELECT * from Groups WHERE groupname= ' + SUBSTRING (@GroupName, 1,5) + ' "); --There is a grammatical 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 parentheses can only be string variables, string constants, or their combination of joins, cannot invoke other functions, stored procedures, and so on. If you want to use it, use a combination of variables, 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: 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

The query came out without results, without declaring the parameter length.

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 ', @GroupName

Summary: Dynamic batching does not access local variables that are defined in the batch process. sp_executesql can have input and output parameters that are more flexible than execute.

Let's take a look at execute, sp_executesql's execution efficiency, first to clear the cache execution plan, and then change the @groupname value superadmin, Commonuser, Commonadmin three times respectively. And then look at the information that it uses caching

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.% ';

Follow the gourd, and then we'll look at the 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 ', @GroupName;

Selectcacheobjtype, ObjType, usecounts, SQL

From sys.syscacheobjects

WHERE SQL Notlike '%cache% '

Andsql notlike '%sys.% ';

Summary:exec generates three separate ad hoc execution plans, and uses sp_executesql to generate only one execution plan, repeated three times, imagine if a library inside, there are many such similar dynamic SQL, and frequently executed, if the use of sp_ ExecuteSQL can improve performance.