Summary of dynamic SQL development basics and experiences

Easy to write SQL, but difficult to master.

NOTE: For the structure of the "person" table in this example, refer to the data table in the "person" text.

I. SQL statements directly deal with the "accumulate" problem of non-numeric Columns

Query requirement: retrieve the firstname of all persons in the person table and separate them with commas.

You can directly use select @ local_variable = expression:

 
Declare @ result varchar (8000) set @ result = ''select @ result = @ result + case when @ result = ''then firstname else ', '+ firstname end from person order by idselect @ result as names
 

We also have a clumsy way to query, that is, through the cursor:

-- Use the cursor declare @ result varchar (8000) Declare @ SQL varchar (50) declare C cursor forward_only read_only forselect firstname from person order by idset @ result = ''open cfetch next from C into @ sqlwhile @ fetch_status = 0 begin set @ result = case when @ result = ''then @ SQL else @ result + ', '+ @ SQL end fetch next from C into @ sqlenddeallocate cselect @ result as names
 

For most SQL queries, we strive not to use cursors, because allocation and release of cursors are not required, which can greatly save database resources and improve efficiency.

 

Ii. Basics of dynamic SQL statements

In No. 1, we have used dynamic SQL. Here we will summarize the frequently used dynamic SQL programming basics.

1. single quotation marks and double quotation marks

Single quotes:

Select ''' as result -- single quotation marks
 

What about double quotation marks? 8 single quotes? If 8 single quotes are true, three single quotes are actually returned. The real double quotation marks can contain "8 single quotes. Of course, they must be added up like the following:

 
Select ''' + ''' as result -- double quotation marks
 

But the more direct method is as follows:

 
Select ''' as result -- double quotation marks
 

That's right, it's six single quotes.

Can single quotes and double quotes be so annoying? This may be because SQL programming is not as advanced.ProgramThe language is simple and straightforward.

2. Define variables and assign values to variables

Let's look at a simple SQL statement:

 
Declare @ A varchar (20), @ B varchar (20) set @ A = 'jeff 'set @ B = 'wong' print @ A + @ B
 

We define variables through declare, assign values to variables through set, or assign values to variables through select:

 
Declare @ A varchar (20), @ B varchar (20) Select @ A = 'jeff 'select @ B = 'wong' print @ A + @ B
 

The difference between set and select assignment: It is said that the one-time assignment of select is more efficient than the one-by-one assignment of set.

3. Exec (@ SQL)

Common SQL statements can be executed directly through Exec

Exec ('select top 10 * From person (nolock) order by id desc ') -- parentheses on both sides
 

You can also execute variable SQL by defining variables, but you must add brackets:

 
Declare @ SQL varchar (2000) set @ SQL = 'select top 10 * From person (nolock) order by id desc 'exec (@ SQL)
 

4. Exec sp_executesql

Sp_executesql supports replacing the parameter values of any parameters specified in the transact-SQL string, but the execute statement does not. Therefore, the transact-SQL string generated by sp_executesql is more similar than that generated by the execute statement. The SQL Server Query Optimizer may match the transact-SQL statement from sp_executesql with the execution plan of the previously executed statement to save the overhead of compiling the new execution plan.

For common SQL statements, this is the same as executing the SQL statement directly with Exec:

Exec sp_executesql n' select top 10 * From person (nolock) order by id desc '-- N must be added
 

Note: The upper-case n must be added before the SQL statement to be executed, and the N is not white. It also has an important meaning!

If the SQL statement we run is defined as a variable, the following SQL statement cannot be executed:

 
Declare @ SQL varchar (2000) set @ SQL = 'select top 10 * From person (nolock) order by id desc 'exec sp_executesql @ SQL
 

If you change the @ SQL variable type from varchar to nvarchar, you can run the following command:

Declare @ SQL nvarchar (2000) set @ SQL = 'select top 10 * From person (nolock) order by id desc 'exec sp_executesql @ SQL
 

Why is it so sad? But you want to torture me? Let's solve this problem:

(1) exec sp_executesql n. The inverted n indicates that the subsequent SQL content is Unicode and also corresponds to the nvarchar type;

(2) Change varchar to nvarchar to execute the SQL statement. This is to convert all parameter values to characters or Unicode and make them part of the transact-SQL string.

5. Put the exec execution result into the variable

For example, to query the number of all records in the person table, you can put the execution result in the variable @ num as follows:

/* Put the exec execution result into the variable num */declare @ num int, @ SQL nvarchar (4000) set @ SQL = 'select @ totalcount = count (0) from person 'exec sp_executesql @ SQL, n' @ totalcount int output', @ num output select @ num as totalcount
 

6. Two types of conversion functions

The following example converts an integer (INT) to a string (varchar ):

 
/* Cast and convert functions */declare @ input intset @ input = 1234 select convert (varchar (50), @ input) + 'abc' as resultset @ input = @ input + 1000 select cast (@ input as varchar (50) + 'xyz' as result
 

These two are usually used for development, and you should be familiar with them.

---------------------------------------------- Split line --------------------------------------------

It is very simple, and I don't expect anyone to print the following text on his or her resume in a proper name:

"XX, YY years of development experience, proficient in ZZ database. "