Dynamic SQL (Chapter Summary), dynamic SQL Chapter Summary

Dynamic SQL (Chapter Summary), dynamic SQL Chapter Summary
1. You can use dynamic SQL to create a subroutine when the dependent object does not exist.


2. dynamic SQL mainly uses EXECUTE IMMEDIATE statements to EXECUTE DML, DDL, DCL, and other statement operations.


3. If binding variables are used, you must use the USING clause in execute immediate to set the required binding variables.


4. You can use the RETURNING or RETURN statement to receive the returned results after the query or update.


5. You can use batch processing to save multiple retrieved data in the database to the set at a time, or use FORALL to set multiple binding parameters to dynamic SQL.
SQL dynamically converts rows and columns

Question: Suppose there is a student renewal table (tb) as follows:
Name course score
Zhang San Language 74
James math 83
Zhang San physical 93
Li Si language 74
Li Si mathematics 84
Li Si physical 94
(The following result is displayed ):
Name, Chinese, Mathematics, Physics
----------------
Li Si 74 84 94
Zhang San 74 83 93
-------------------
*/

Create table tb (name varchar (10), course varchar (10), score int)
Insert into tb values ('zhang san', 'China', 74)
Insert into tb values ('zhang san', 'mat', 83)
Insert into tb values ('zhang san', 'Physical ', 93)
Insert into tb values ('Li si', 'China', 74)
Insert into tb values ('Li si', 'mat', 84)
Insert into tb values ('lily', 'Physical ', 94)
Go

-- SQL server 2000 static SQL indicates that the course only includes three courses: Chinese, mathematics, and physics. (Same as below)
Select name as name,
Max (case course when 'China' then score else 0 end) language,
Max (case course when 'mate' then score else 0 end) math,
Max (case course when 'physical 'then score else 0 end) Physical
From tb
Group by name

-- SQL server 2000 dynamic SQL refers to three courses, including Chinese, mathematics, and physics. (Same as below)
Declare @ SQL varchar (8000)
Set @ SQL = 'select name'
Select @ SQL = @ SQL + ', max (case course when''' + course + ''' then score else 0 end) [' + course + ']'
From (select distinct course from tb) as
Set @ SQL = @ SQL + 'from tb group by name'
Exec (@ SQL)

A where problem in dynamic SQL statements

The first parameter after sp_executesql is an SQL statement, which must be of the ntext/nchar/nvarchar type. Therefore, N must be added before the string when @ strSQL is assigned a value;
The second parameter after sp_executesql is an internal parameter table. All parameters used in SQL statements are written in a string separated by commas;
The third parameter after sp_executesql is an external parameter, which must correspond to an internal parameter table. If it is an output parameter, it must contain outupt.
Therefore, the complete statement is as follows:
-- Execute dynamic SQL with sp_executesql, which is suitable for SQL server 2005 and later declare @ strSQL nvarchar (max); declare @ shuju1 nvarchar (10) = n'test'; declare @ ID int; declare @ mingcheng nvarchar (10) = 'a '; set @ strSQL = n' select @ ID = ID from ##' + @ mingcheng + 'where' + @ mingcheng + '= @ shuju1'; exec sp_executesql @ StrSQL, n' @ ID int output, @ shuju1 nvarchar (10) ', @ ID output, @ shuju1; select @ ID;