SQL stored procedure syntax

I. Notes

 -- Single-row comment. From this point to the end of the line, it is used to annotate the SQL syntax, similar to C ++, C //
 Multi-line comment, similar to C ++, C #

Ii. variables (INT, smallint, tinyint, decimal, float, real, money, smallmoneysql syntax, text, image, Char, varchar ......)
Syntax:
Declare
 {
 {@ Local_variable data_type}
 } [,... N]
For example:

Declare @ ID int -- declare a variable named @ ID. The type is int type.

3. Print the variable value in the SQL Server window

Syntax:
Print 'any ASCII text' | @ local_variable | @ function | string_expr

 

Iv. Variable assignment

 For example:

 -- Extract the ID of the first row of data from the data table, assign the value to the variable @ ID, and print it out.
Declare @ ID int
Set @ ID = (select top (1) categoryid from categories)
Print @ ID

In SQL, we cannot directly assign values to variables like code, for example, @ ID = 1. To achieve this function, you can write as follows:

Declare @ ID int
Set @ ID = (select 1) -- similar to @ ID = 1
Select @ ID = 1 -- similar to @ ID = 1
Print @ ID

5. Variable operations (+,-, * SQL syntax ,/,......)

The variable declaration should be omitted if necessary
Set @ ID = (select 1 + 5) -- similar to @ ID = 1 + 5
Set @ ID = (select 1-@ ID) -- similar to @ ID = 1-@ ID

6. Comparison Operators
? > (Greater ).
? <(Less ).
? = (Equals ).
? <= (Less than or equal ).
? > = (Greater than or equal ).
? ! = (Not equal ).
? <> (Not equal ).
? ! <(Not less ).
? !> (Not greater ).
Nothing to say

VII. Statement block: Begin... End
Use multiple statements as a block, similar to {} in C ++ and C {}
For example:
Begin
 Set @ id1 = (select 1)
 Set @ Id2 = (select 2)
End

8. If, if... Else...
Syntax:
If boolean_expression_r
 {SQL _statement | statement_block}
[Else
 {SQL _statement | statement_block}]
For example:

If @ ID is not null
 Print '@ ID is not null
If @ ID = 1
Begin
 Set @ ID = (select 1 + 1)
End
Else
Begin
 Set @ ID = (select 1 + 2)
End

The preceding example uses the comparison operators, statement blocks, and if syntax.

 

9. Execute other stored procedures Exec
For example

Exec DBO. [sales by year] @ beginning_date = '2017/90', @ ending_date = '2017/08'

 

10. Transactions

Syntax:

Begin Tran [saction] [transaction_name | @ tran_name_variable]

For example

Begin tran
-- Perform some operations, such as insert...
If @ error <> 0
Begin
 Rollback tran
End
Else
Begin
 Commit tran
End

11. cursor

We can use the SELECT statement in the stored procedure to retrieve each row of data for operations, which requires a cursor.

Syntax:
Declare cursor_name cursor
[Local | Global]
[Forward_only | scroll]
[Static | keyset | dynamic | fast_forward]
[Read_only | scroll_locks | optimistic]
[Type_warning]
For select_statement

[For update [of column_name [,... n]

For example:

Declare @ au_id varchar (11), @ au_fname varchar (20)-declare the variable
-- Declare a cursor
Declare authors_cursor cursor
Select au_id, au_fname from authors
-- Open the cursor
Open authors_cursor
-- Retrieve Value
Fetch next from authors_cursor into @ au_id, @ au_fname
-- Cyclically retrieve the cursor Value
While @ fetch_status = 0
Begin
 Print @ au_id
 Print @ au_fname
 Print''
 Fetch next from authors_cursor
 Into @ au_id, @ au_fname
End
Close authors_cursor-close the cursor
Deallocate authors_cursor -- release cursor