SQL Server while, case, if... else... Try catch... object

As we mentioned earlier, the stored procedure and view are as follows,

Next, let's take a simple look at the while, case, if... else objects in the database.

1 While

While is a command object that executes repeated operations based on whether the value of bool is true or false.

Syntax:

While Condition

Begin

.....

End

Example

Declare @ start int
Set @ start = 1
While @ start <= 10
Begin
Print @ start
Set @ start = @ start + 1
End

2 case

Case returns a specific value based on a set of logic,
Declare @ value int
Set @ value = 1
Select case @ Value
When 1 then 'guanbi'
When 2 then 'dakai'
End

The result returned in the preceding example is as follows:

The returned result of the following case statement is, as shown in figure

Declare @ value int
Set @ value = 1
Select case @ Value
When 1 then 'shutdown'
When 2 then 'open'
End

 

We will note that if then is followed by a Chinese character, the returned result is "?". This may not happen in other versions of the database. I have not tried it. I use SQL Server 2005,

3 if... else

This should be a stranger to the two above. If the condition is met, the SQL statement of the condition is executed, and the SQL statement of the next condition is executed, here is a simple example to illustrate this point.

Syntax

If condition

Statement to be executed

Else

Statement to be executed

....

If 4> 1
Print 'true'
Else
Print 'false'

The execution result is true.

More complex,

Declare @ I int
Set @ I = 1
While (@ I <= (select count (*) from chengji ))
Begin
Select Xingming
, Fenshu
, Case fenshu
When 100 then 'youxiu'
When 60 then 'hege'
When 30 then 'bucke'
End
From chengji
Set @ I = @ I + 1
End

4 try... catch...

Example:

Begin try
Declare @ x int
-- Divide by zero to generate error
Set @ x = 1/0
Print 'COMMAND after error in try Block'
End try
Begin catch
Print 'error detected'
End catch

 

By now, these simple database objects have been introduced. Of course, these are all superficial. Although this is the case, it is also the basis. Then, complicated scripts are composed of multiple simple scripts, it is called compound simplicity. As long as we accumulate these simplest SQL statements, we can also write SQL statements that are quite different from each other.

Beginner: xiaobin