MS-SQL control statement

Begin... End

The syntax is as follows: Begin <command line or program block> endbegin... End is used to set a program block... All programs in end are considered as a unit to execute begin... End is often used in conditional statements, such as if... Else.
In begin... Another begin… can be nested in the end... End to define another program block.

 

If... Else

The syntax is as follows: if <condition expression> <command line or program block> [else [condition expression] <command line or program block>]

NOTE: If or else can only execute one command if no program block is used. If... Else can be nested.

Instance:

View code

Declare @ x int, @ Y int, @ Z intselect @ x = 1, @ Y = 2, @ z = 3if @ x> @ Y print 'x> y' -- print the string 'x> y' else if @ Y> @ zprint 'y> Z' else print 'z> the running result of 'y' is as follows z> Y note: up to 32 levels can be nested in transact-SQL

 

Case

The case command has two statement formats: Case <formula> when <formula> then <formula>... When <formula> then <formula> [else <formula>] endcasewhen <condition expression> then <formula> when <condition expression> then <formula> [else <formula>] End

Instance:

View code

First: select case when price is null then 'not yet priced' when price <10 then 'very reasonable title 'when price> = 10 and price <20 then' coffee table title 'else' expensive book! 'End as "price category", convert (varchar (20), title) as "shortened title" from pubs. DBO. titlesorder by price type 2: Select au_fname, au_lname, case state when 'CA' then' California 'when' KS 'then' Kansas 'when' tn 'then' Tennessee 'when' or 'then' Oregon 'when' mi 'then' michigan 'when' in 'then' Indiana 'when' MD 'then' Maryland 'when' ut 'then 'utah' end as statenamefrom pubs. DBO. authorsorder by au_lname select Top 2 ID, uid, CN, Case Len (CN) When 3 then 'california 'when 2 then 'Kansas 'end as statenamefrom conf_users

Update case

use panguupdate employeeset e_wage =casewhen job_level = ’1’ then e_wage*1.08when job_level = ’2’ then e_wage*1.07when job_level = ’3’ then e_wage*1.06else e_wage*1.05end

 

While... Continue... Break

The syntax is as follows: while <condition expression> begin <command line or program block> [Break] [Continue] [command line or program block] The endwhile command will repeatedly execute the command line or program block when the set condition is set. The continue command allows the program to skip the statement after the continue command and return to the first line of the while loop command. The break command completely jumps out of the loop and ends the while command execution. While statements can also be nested.

Instance:

While nesting

Declare @ x int, @ Y int, @ C intselect @ x = 1, @ Y = 1 while @ x <3 beginprint @ X -- print the value of variable X while @ Y <3 beginselect @ C = 100 * @ x + @ yprint @ C -- print the variable C select @ Y = @ Y + 1 endselect @ x = @ x + 1 select @ Y = 1end

 

Return

Return [integer] The return command is used to end the execution of the current program and return it to the previous program or other programs that call it. You can specify a return value in parentheses.

 

 

View code

Declare @ x int @ Y intselect @ x = 1 @ Y = 2if x> yreturn 1 elsereturn 2 if no return value is specified, the SQL Server System returns an internal value based on the execution result.