SQL stored procedure BASICS (Process Control)

Here we will talk about the process control of the stored procedure. if else, case, while, there is no for loop here. These are not as good as c, c ++, c # and other languages.

First, let's look at the use of if else
Copy codeThe Code is as follows:
If condition
Begin
SQL statement
End
Else begin
SQL statement
End

Let's look at a simple example.
Copy codeThe Code is as follows:
Declare @ id int -- declare Variables
Set @ id = 5 -- set the kitchen Initial Value
If (@ id = 1)
Begin
Print 'right'
End
Else if (@ id = 0)
Begin
Print 'error'
End
Else
Begin
Print 'default'
End

As a result, I think everyone should know it and I will not write it out. The begin and end here can also be left empty, but it is recommended to write it. This is like the {} of c ++ code, which indicates a piece, the written layers are clearer.

Of course, if Nesting is also possible.

Ii. Use of While

Let's calculate the sum from 1 to 100.

Copy codeThe Code is as follows:
Declare @ I int, @ sum int
Set @ I = 0
Set @ sum = 0
While @ I <= 100 -- start Loop
Begin
Set @ I = @ I + 1 -- Auto increment 1
Set @ sum = @ sum + @ I
End
Print @ sum
Result
-----------

Iii. Use of multiple Case options

Copy codeThe Code is as follows:
Declare @ iret int, @ var varchar (10)
Set @ var = 'A'
Select @ iret =
Case when @ var = 'A' then 0
When @ var = 'B' then 1
When @ var = 'C' then 2
When @ var = 'D' then 3
Else-1
Print @ iret

Result
-------

The above basic process control can basically meet our daily development needs, so much has been thought of for the time being. When we think of other situations, we will add them as appropriate.