SQL control statements

SQL control statements
Global variables of SQL variables:

Global variables are defined and maintained by the system using two @ prefixes. They cannot be defined or assigned by users!

Common global variables are as follows:

@ Version: Obtain the current SQL Server version.

EG:

select @@version

Display Information:

Certificate ---------- Microsoft SQL Server 2008 R2 (RTM)-10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200 :)

@ Identity: the value of the ID column returned by the preceding SQL statement

EG:

Insert into hous (name, price) values ('room 1', 299) select @ IDENTITY as ID column

Display Information:

(One row is affected)
ID column
---------------------------------------
5

(One row is affected)
@ Rowcount: returns the number of affected rows of the last SQL statement.

Insert into hous (name, price) values ('room 1', 299) select @ rowcount as number of affected rows

Display Information:

(One row is affected)
Number of affected rows
-----------
1

(One row is affected)

@ Servername: name of the local server

EG:

select @@servername

Display Information:

Slave --------------------------------------------------------------------------------------------------------------------------------
DESKTOP-M5OLTTN

(One row is affected)

@ Error: error number of the last SQL error

EG:

Insert into hous (name, price) values ('room 1') select @ ERROR as ERROR number

Display result:

Message 109, Level 15, status 1, 1st rows
The number of columns in the INSERT statement is greater than the number of VALUES specified in the VALUES clause. The number of VALUES in the VALUES clause must match the number of columns specified in the INSERT statement.

Error Code
-----------
109

(One row is affected)

@ Servicename: name of the SQL server on the computer

EG:

select @@servicename

Display result:

Slave --------------------------------------------------------------------------------------------------------------------------------
MSSQLSERVER

(One row is affected)

@ Language: name of the currently used language

EG:

select @@language

Display result:

Slave --------------------------------------------------------------------------------------------------------------------------------
Simplified Chinese

(One row is affected)

@ Max_connections: Maximum number of simultaneous connections that can be created

EG:

select @@MAX_CONNECTIONS

Display result:

-----------
32767

(One row is affected)

@ Timeticks: Number of microseconds per scale on the current computer

EG:

select @@timeticks

Display result:

-----------
31250

(One row is affected)

@ Trancount: number of transactions opened by the current connection

EG:

select @@TRANCOUNT

Display result:

-----------
0

(One row is affected)

Local variable declaration Syntax:

Declare @ variable datatype

Here, variable is the name of the local variable, and ype is the data type.

Assignment Syntax:

Local variables can be copied in two ways.

1. set @ variable = value

2. select @ variable = value

Difference between set and select

A select statement is generally used to assign data in a table to a variable.

The set statement does not support assigning data from a table to a variable.

Tip:

Assigning a value to a variable is more efficient than 'set. Set does not support assigning values to multiple variables at the same time

SQL output statement

There are two Output Modes in SQL server

1. print local variables or strings

2. select local variables or strings

The output data of the select statement is a special usage of the query statement.

Data Type Conversion

Syntax:

Cast (expression as data type)

Convert (data type, expression [, style ])

"[]" Is an optional part.

Logical control statement begin-end

Syntax:

Begin

Statement or statement Block

End

Similar to "{}" in C #, it indicates the quick start and end of the statement. One statement block can contain another statement Block

IF-else and while are similar to C #. Here we will not describe the Case multi-branch statements one by one.

The case multi-branch statement is similar to the swith selection structure in C #, so the syntax is different.

Case Syntax:

Case

When condition 1 then result 1

When condition 2 then result 2

......

[Other else results]

End

"[]" Is an optional part.

It is worth noting that if Else is omitted and the when condition is not true, the returned result is null.

Batch Processing-go

It is a collection of one or more SQL statements, that is, an executable unit. Each batch can be compiled into a single executable Unit to improve the execution efficiency. If the batch processing contains multiple SQL statements, all optimization steps for executing these statements are compiled in a single execution unit.