What is dynamic SQL?? What is static SQL, dynamic SQL is the dynamic embodiment of where???

First of all, the so-called SQL dynamic and static, refers to when SQL statements are compiled and executed, both are used in SQL embedded programming, which is referred to as embedded refers to the SQL statement embedded in the high-level language, rather than the type of microcontroller embedded programming.
In a high-level language, if an SQL statement is embedded, and the main structure of the SQL statement is clear, for example, there is a SQL "select * from T1 where c1>5" to execute in a piece of Java code, in the Java compilation phase, You can pass this SQL to the database management system to analyze, the database software can parse this SQL, generate database executable code, such SQL is called Static SQL, that is, in the compilation phase can determine what the database to do.
And if the embedded SQL is not explicitly given, such as a variable sql:string SQL that defines a string type in Java, then executes the SQL using the Execute method of the PreparedStatement object, The value of the SQL may be equal to one SQL read from the text box or SQL entered from the keyboard, but what is not determined at compile time, only wait until the program is run, in the process of execution can be determined, this SQL is called Dynamic SQL. For example, each database software has the ability to execute the SQL statement interface, the interface received SQL is dynamic SQL, because the database vendors do this interface, do not know what the user will enter the SQL, only after the interface executes, the user's actual input, only to know what SQL is.
Also note that in SQL if some parameters are not determined, such as "select * from T1 where C1> and c2< ", this statement is static SQL, not dynamic SQL, although the value of the individual parameters is not known, but the entire SQL structure has been determined that the database can be compiled, in the execution phase only need to add the values of individual parameters.

Dynamic Sql:code is executed dynamically. It is generally a block of SQL statements that is dynamically combined based on user input or external conditions. Dynamic SQL can play a powerful role in SQL, and it is easy to solve some problems that other methods are difficult to solve. Believe that people who have used dynamic SQL can appreciate the convenience, however, dynamic SQL is sometimes less likely to perform performance (efficiency) than static SQL, and is inappropriate to use, often with security implications (SQL injection attacks). Dynamic SQL can be executed either by execute or sp_executesql. (from MSDN)

EXECUTE

Executes a command string, string, or one of the following modules in a Transact-SQL batch: A system stored procedure, a user-defined stored procedure, a scalar-valued user-defined function, or an 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 the execution string or command

sp_executesql

Executes Transact-SQL statements or batches that can be reused or generated dynamically. A Transact-SQL statement or batch can contain embedded parameters. In terms of batch processing, name scoping, and database context, sp_executesql behaves the same as EXECUTE. The Transact-SQL statement or batch in the sp_executesql stmt parameter is compiled only when the sp_executesql statement is executed. The content in the stmt is then compiled and run as an execution plan. The execution plan is independent of the execution plan for the batch named sp_executesql. The sp_executesql batch cannot reference a variable declared in a batch that calls sp_executesql. sp_executesql a local cursor or variable in a batch is not visible to the batch that calls sp_executesql. Changes made to the database context are only valid until the end of the sp_executesql statement.

If you change only the parameter values in the statement, sp_executesql can be used instead of the stored procedure to execute Transact-SQL statements more than once. Because the Transact-SQL statement itself remains the same, only the parameter values change, so the SQL Server query optimizer may reuse the execution plan that was generated when it was first executed.

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 re-use the execution plan, improve execution efficiency. There is also the use of sp_executesql to improve security, of course, not to completely abandon the execute, in particular cases, execute is more convenient than sp_executesql, such as dynamic SQL string is a varchar type, Not a nvarchar type. sp_executesql can only execute strings that are Unicode, or constants or variables that can be implicitly converted to ntext, while execute performs both types of strings.

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

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

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

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

Code

DECLARE @GroupName VARCHAR (50);

SET @GroupName = ' superadmin ';

EXECUTE (' SELECT * from Groups where groupname= ' + SUBSTRING (@GroupName, 1,5) + '); -There are grammatical errors 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 connection combinations, and cannot invoke other functions, stored procedures, and so on. If you want to use it, use a combination of variables, as shown above.

CodeDECLARE @Sql VARCHAR (200);
DECLARE @GroupName VARCHAR (50);

SET @GroupName = ' superadmin ';
SET @Sql = ' SELECT * from Groups WHERE [email protected] '
--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 WHERE [email protected] '
PRINT @Sql;
EXEC sp_executesql @Sql, N ' @GroupName NVARCHAR ', @GroupName
The query came out with no result and no argument length.


DECLARE @Sql NVARCHAR (200);
DECLARE @GroupName NVARCHAR (50);

SET @GroupName = ' superadmin ';
SET @Sql = ' SELECT * from Groups WHERE [email protected] '
PRINT @Sql;
EXEC sp_executesql @Sql, N ' @GroupName NVARCHAR ', @GroupName

Summary: Dynamic batching cannot access local variables defined in a batch. The sp_executesql can have input and output parameters, which is more flexible than execute.

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

Code

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);


SELECT Cacheobjtype, ObjType, usecounts, SQL
From sys.syscacheobjects
WHERE SQL not is like '%cache% '
and SQL not like '%sys.% ';

As shown

According to the gourd painting scoop, then we look at the efficiency of sp_executesql implementation.

CodeDBCC Freeproccache;


DECLARE @Sql NVARCHAR (200);
DECLARE @GroupName NVARCHAR (50);

SET @GroupName = ' superadmin '; --' commonuser ', ' commonadmin '
SET @Sql = ' SELECT * from Groups WHERE [email protected] '
EXECUTE sp_executesql @Sql, N ' @GroupName NVARCHAR ', @GroupName;


SELECT Cacheobjtype, ObjType, usecounts, SQL
From sys.syscacheobjects
WHERE SQL not is like '%cache% '
and SQL not like '%sys.% ';

The execution results are as follows:

Summary:exec generated three independent ad hoc execution plans, and sp_executesql only generated one execution plan, reused three times, imagine if a library, there are many such similar dynamic SQL, and frequent execution, if the use of sp_ ExecuteSQL can improve performance.

What is dynamic SQL?? What is static SQL, dynamic SQL is the dynamic embodiment of where???