(2.3) DDL enhancements-Process Control and dynamic SQL

1. Process Control

In T-SQL, there are 8 keywords associated with a Process Control statement:

BEGIN ... END	Break
Goto	CONTINUE
IF ... ELSE	While
RETURN	WAITFOR

In fact, you can add one, that is go, let's explain the meaning of the next one.

(1) GO: Batch Commit statement, the equivalent of everything before GO to the system (usually look like it is not OK, but in sqlcmd Login DOS interface Use batch will be used), the destruction of the recovery of the recycling of the collection and so on, demo

　　　　

(2) BEGIN ... END

This is actually a logical block, similar to the major language of the {}

(3) While/break/continue

　　　

     
      while (expression)--run the statement statement in Begin End If the expression is true, or skip begin　　　　statement;  End


Break: Jump directly out of the loop
Continue: The subsequent code of this cycle does not execute, directly start the next cycle
It's too simple to show.

(4) IF ... ELSE

    IF -- executes the statement sentence in the middle of the begin end if the expression is established;    BEGIN               statement;     END        ELSE  -- if the expression in the if does not hold, then jumps to the else, executes the statement1;    BEGIN               statement1;     END         -Note: If you do not write begin end then only one statement is owned by the IF/ELSE

(5) Goto

Goto is a disruptive guy, you can jump to any point of definition, basically no use, generally used to jump to the wrong

　　　　

--goto jump for loop function
Basic usage:
Define the point name:
Goto Definition Point Name
Case:
DECLARE @n int 　　 Set @n=1Print_point:Print @n 　　 IF(@n < 3)begin    　　 Set @n=@n+1     　　 GotoPrint_pointEnd 　　 Print 'The @n alread > 3'

Results

　　　　

(6) return

Quickly end any batch, and the statement after return is no longer executed, as shown below

, and found that print 5 did not perform as well. Remember, the batch is ended with Go

(7) WAITFOR

Delay and fixed point

(7.1) WAITFOR DELAY time delay time (default is seconds) after continuing sequential execution

(7.2) waitfor time is executed sequentially

The demo is as follows

  Print1  waitfor'00:00:05'--Notice how the time format is written   Print2

Print 1
waitfor time ' 16:02:05 '--Note the format
Print 2

　

2. Dynamic SQL ( other types must be converted to character types to use dynamic SQL)

(1) Variable definition

DECLARE @n int

Analysis: Declare: for keyword, @n:@ for variable, n variable name, int for variable type;

Set @n = 1; Select @n=1

Analysis: Set is an assignment keyword, and select can also be used to assign a value

If used, you can use @n directly

(2) Execute dynamic SQL

(2.1) Execute, can be abbreviated as exec

Basic form: Exec SP or EXEC (string), shown below

　　　　　　　　

A basic demonstration of dynamic SQL

        Declare @sql varchar( +),@t int        Declare @n int            Set @n=2            Set @t=Ten            Set @sql = 'Select'+cast(@n  as varchar)+'WHERE ten ='+cast(@t  as varchar)--note that other types here must be converted to character types to use dynamic SQLExecute(@sql)--can be abbreviated as exec

The code results are as follows

　　　　　　

Execute connection to another server operation

　　　　

This means that the operation is done on the linked server.

(2.2) sp_executesql is actually a stored procedure

To use exec to execute it, the common form is: exec sp_executesql @sql, but @sql compared to Unicode form, demonstrated as follows

(Do not know what is Unicode see the string type in the 5th in https://www.cnblogs.com/gered/p/9117522.html, the constant string is preceded by an n,)

　　　　　　

　　　　sp_executesql can do parameter input and output operation in dynamic SQL

"1" Input

Declare @sql nvarchar (+),@numintset@sql= N'select @num =1 ' exec @sql, N'@num int output',@num  output   -- The print @num must be defined here as well 

Results: 1

"2" input and output combination

 declare   @sql  nvarchar  (1000 ),  @num  int  set   @sql  =  n "  Select @num =1 where @num2 =   " exec  sp_executesql  @sql , N "  @num int output, @num2 int  " ,  @num  output, @num2 =10--if you have multiple parameters, you can  print   @num 
 
 Result: 1  

　　　　

Judging the quality and difference between execute and sp_executesql

Same behavior: The statement or batch used is compiled at execution time, and the compiled content runs as an execution plan

Different behavior: Execute must turn everything into a string, sp_executesql can enter the argument.

Precautions:

(1) Data type conversion problem, must be converted to character type in execute, Unicode format must be used in sp_executesql

(2) string boundary problem: In fact, the single quote ' Problem

For example, under normal circumstances

DECLARE @str char (6)
Set @str = ' a '
Select 1 Where @str = ' a '

When switching to dynamic SQL

Declare @str Char(6),@sql varchar( +)Set @str = 'a'Set @sql = 'Select 1 where" "+@str+" "="'a" "exec(@sql)
--single quotation marks are transferred when they are used, that is, ' expressed as '


To give an example of a date, use the error directly

　　

The correct code is as follows:

　　

--Want to execute the statement select 1 where getdate () > ' 20180601 '
DECLARE @str datetime, @sql varchar (1000)
Set @str = ' 20180601 '
--Dynamic SQL statements
Set @sql = ' Select 1 where getdate () > "[email protected]+"]
EXEC (@sql)

(3) Table Variable field problem

--Want to query all the contents of the Test101 table
DECLARE@table_namechar()set@table_name= ' test101 ' Select *  from @table_name

This error

--Want to query all the contents of the Test101 table

DECLARE @table_name Nchar (30)
Set @table_name = N ' test101 '
EXEC sp_executesql n ' select * from @table_name ', n ' @table_name char (+) ', @table_name

It also gives an error.

　　　　

The correct opening method is as follows:

　　　　

DECLARE @table_name Nchar (+), @sql nvarchar (100)
Set @table_name = N ' test101 '
Set @sql = ' select * from ' [email protected]_name
EXEC (@sql)

　　　

(4) You can no longer use Exec to assign values to variables in dynamic SQL, and you may use sp_executesql to assign values

　　

(2.3) DDL enhancements-Process Control and dynamic SQL